#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0

    Many to Many Relationship


    Hello All,

    I would like to create a many to many relationship between tables. I am not sure of how to do this, I have moved from access to postgres.

    Any help?

    I know how to create a one to many, setting a primary on one table foreign on the other etc. in the constraints.

    Thanks in advance
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    are you new to Google as well?

    The first result is already an excellent explanation. In a nutshell: You have a separate association table with one column for each primary key of the original tables.

    Comments on this post

    • LukeJohnson agrees : Thank you, very helpful :)
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Coming from experience with Access you might be missing the point of a many-to-many. In data modeling the best answer is usually not quite as simple as just linking two things together through a junction table -- most of the time you want the junction itself to carry some extra information about the relationship itself instead of merely linking them.

    Also, in a rich data schema primary keys will usually not be single columns, though you may create arbitrary unique integer keys in addition to a natural primary key. In this case you will want to reference every column of the natural key, which results in a wider table than the typical two-column junction table, even if you're just relating two tables together.

    The major advantage here is that natural keys themselves provide a complete description of the referenced table (if your schema is normalized), so instead of a 3-way join to get the data you need, in a normalized schema you usually only need to look up the junction table itself or in the case of what would be a 5-way join within a trivially structured web ORM framework (fact_table + junction_table + fact_table + junction_table + fact_fable, with a select only from the two end fact tables) linking by meaningless integer keys you can instead join just the two junction tables and have your answer -- which can be considerably faster.

    Consider persons and organizations. An organization is made of many members, and a person can belong to many organizations at once:

    [code=SQL]CREATE TABLE person
    (name varchar(30) PRIMARY KEY);

    CREATE TABLE organization
    (name varchar(30) PRIMARY KEY);

    CREATE TABLE roster
    (organization varchar(30) REFERENCES organization NOT NULL,
    person varchar(30) REFERENCES person NOT NULL,
    PRIMARY KEY (organization, person));[/code]
    There are other ways to do this but this is the simplest normalized way. But this misses most of the point of knowing a roster! (For the sake of a simple example we'll ignore the fact that storing just a name as a primary key also misses the point of knowing anything about a person or organization in most cases...) People aren't born belonging to organizations, organizations don't exist forever and neither do people, and a person could be a member one day, not the next, and then return and we would have no idea about that with the simple junction table "roster" above. We also don't know the role of the person in the organization or anything else.

    And this is why I say, a simple junction table is nearly always the wrong answer. The interesting thing about data is almost always the way that basic elements relate to each other, and the important part about the way (especially for a business system) is the details inherent to the relationship itself.

    So consider this:
    [code=SQL]CREATE TABLE person
    (name varchar(30) PRIMARY KEY);

    CREATE TABLE organization
    (name varchar(30) PRIMARY KEY);

    CREATE TABLE role
    (code varchar(20) PRIMARY KEY);[/code]
    These are basic facts -- the people, the organizations and roles that are possible within an organization. Now we're going to make a junction table that isn't just a many-to-many, it is a documented relationship history.
    [code=SQL]CREATE roster
    (id SERIAL PRIMARY KEY,
    organization varchar(30) REFERENCES organization NOT NULL,
    person varchar(30) REFERENCES person NOT NULL,
    role varchar(20) REFERENCES role DEFAULT 'member' NOT NULL,
    span tstzrange DEFAULT tstzrange(current_timestamp, NULL, '[)') NOT NULL,
    EXCLUDE USING gist (organization WITH =, person WITH =, span WITH &&));[/code]
    Now we know who belonged to what organization over what time period, have enforced that the combination of organization, person and span must be unique (a person can't belong to the same organization at the same time twice, but can at different times), and we know the context/role of the person's membership with the organization. Here also there is a numerical id -- which actually sucks because they don't mean anything, but here it is better than trying to pin down the span part of the relationship in queries (to locate within a span use range operators).

    Most of the time you're going to find the answer "a many-to-many is a junction table" but that is almost always not actually true in a real application where the developer has thought through his data a bit. Almost any relationship has details that are far more interesting than simple linkage between to basic elements -- and that means that really, a many-to-many should almost always be a useful table of its own -- something like a roster history in the case of organization memberships, or sales logs, or trade histories, or addresses, or components in a product, or serialized components in an instance of a product, etc.

    Comments on this post

    • LukeJohnson agrees : Thankyou, great help (Y)
    Last edited by zxq9; June 28th, 2013 at 08:52 PM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Location
    Swindon, UK
    Posts
    8
    Rep Power
    0

    Current Layout


    Hi, thanks a bunch super helpful.

    This was how my relationships were laid out in Access.[IMG]Relationships[/IMG]

    Thanks again guys.

IMN logo majestic logo threadwatch logo seochat tools logo