Web Analytics Made Easy -
StatCounter some advice and help with a db schema - haven't done this in a LONG time - CodingForum

Announcement

Collapse
No announcement yet.

some advice and help with a db schema - haven't done this in a LONG time

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • some advice and help with a db schema - haven't done this in a LONG time

    I haven't played around with my own database creation in a few years, and I see that MySQL has changed a LOT,
    and I've forgotten the little that I knew. I'm a real newb, and plan on sticking with WordPress development instead of raw db development...

    I've put together a lengthy outline in html that i'd like to put into a searchable db. I'm simply outlining persons and their relationships,
    including organizations, where any one person can have multiple associations with any other person and any other organization.
    (Please see image of a partial outline) This is as simple as

    » Barnes (knows) » ul li Moore (who knows) » ul li de Mohrenschildt ...
    and Barnes (also knows) » Hunt (who will also know...) » »»»




    I found the following that almost does what I need, but i have no idea how to include Organizations and rel_types tables,
    (maybe relation table could contain rel_types for both persons and orgs...?)

    Code:
    CREATE TABLE person
    (
        person_id INT NOT NULL AUTO_INCREMENT,
        ...
        PRIMARY KEY (person_id)
    );
    CREATE TABLE relationship
    (
        rel_id INT NOT NULL AUTO_INCREMENT,
        person_id1 INT NOT NULL,
        person_id2 INT NOT NULL,
        reltype_id TINYINT,
        PRIMARY KEY (rel_id),
        UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
        KEY has_relationship_to (person1_id,reltype_id),
        KEY has_relationship_by (person2_id,reltype_id)
    );
    CREATE TABLE relation
    (
        reltype_id TINYINT NOT NULL AUTO_INCREMENT,
        rel_name VARCHAR(20),
        PRIMARY KEY (reltype_id),
        UNIQUE KEY (rel_name)
    );
    INSERT INTO relation (relation_name) VALUES
    ('friend'),('follower'),('foe'),
    ('forgotabout'),('forsaken'),('fixed');
    someone suggested this to me:

    "concept of Party can be added. Party can be a person, or organization, or something else that interacts with the system.
    For instance if RDBMS supports enums, you can have

    PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

    Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY)
    and store their attributes separately, or store all attributes in PARTY table."

    and I think I'm interested in what this PARTY concept is, but mainly I'm asking for some kind person's advice and a quick start schema
    that would just get me started. If someone were to look at the image I've supplied to get an idea of what I'm trying to do, with plenty
    of room for different outline layout, the queries I wrote up above can be used or ignored for a better, simpler design...?

    Also, i've searched Google for "outer_affinity" and "has_relationship_to" and cannot find ANYTHING relating to mysql on these. I have
    NO idea what they are ...

    I DO like the idea of the different relation types, "friend," "foe," "follower," etc... that goes well with the type of relationships I'm defining.

    I'm very grateful for anyone who can help me with this...

    regards,
    GN

  • #2
    First step is to determine what tables you need to hold the data once it is fully normalised. This hasn't changed since relational databases were invented apart from the addition long ago of 4th, 5th and 6th normal forms to cater for a few exceptional cases.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

    Comment


    • #3
      thanks felgall - I think I asked the question the wrong way. What I'm looking for is help with tables like the sample I described above, with persons, relationships and rel_types, but including organizations.

      i'm not a db programmer, and don't have plans to be - my skills and work lie elsewhere, and I don't need or have the time to learn MySQL fluently just to tackle this side project. I've been trying to get this solved for three days now, and all the searches I've tried have availed me nada.

      I'd be very grateful for your help with this.

      What's with this "PARTY" concept someone told me about where all persons could be contained in one table and orgs in another, rel_types in another, allowing for associating any one person or organization with any number of other persons or orgs...? i found nothign with google.

      All I'm trying to do is get output such as "John (owned) » Acme Co. (which employed) » Thomas (who knew) » Lisa (who knew) » Robert (who knew) » Sally (who knew) » John..." AS WELL AS "Acme Co. (employed) » Rodriquez (who knew) » Sally..."

      I can handle the queries and the html output. Queries I find easier once I have the data structured.

      I'd very much appreciate anyone's help with this.

      regards,
      GN

      Comment


      • #4
        Normalisation is the process of taking all the fields and relationships and determining the optimal arrangement of those fields into tables such that the relationships are maintained and data isn't unnecessarily duplicated.

        To do what you are asking means you need to perform database normalisation.

        The answer to your question no matter how many times you ask it in how many different ways is NORMALISATION.

        Try Googling for more info - there should be billions of pages of results on how to do it.
        Stephen
        Learn Modern JavaScript - http://javascriptexample.net/
        Helping others to solve their computer problem at http://www.felgall.com/

        Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

        Comment


        • #5
          The Party Model is a very scalable DB design. I use it often. A good read on it is*Silverstone Data Model Resource Book Vol.1, 2, and 3
          To save time, lets just assume I am almost never wrong.

          The XY Problem
          The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

          Make A Donation https://www.paypal.me/KevinRubio

          Comment

          Working...
          X