Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    6

    Design Question: One to One relationship accross 3+ tables?

    Hi,
    I have a question about how to structure a particular part of my database. I have three tables: paths, documents and packages. Each document or package can be linked to a path, but each path may only be linked to either one document or one package. How would you structure this? I can think of three solutions, but none are ideal:

    1. Have a documentId and a packageId field in the paths table. Not good because more may needed to be added later, doesn't stop you having multiple links.
    2. Have a pathId field in the documents and packages table. No repeating groups, but can still have multiple links.
    3. Have some sort of "joinTable" and "joinId" fields in the paths table. Only one link, but can't easily perform joins.


    Can anyone make any suggestions? I'm using MySQL if it makes any difference.

    Thanks,
    Jack

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's how i would do it: one table for both documents and packages, with one column to indicate which it is, and another column for the path as VARCHAR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    6
    Ah, but documents and packages are completely different, with different columns etc. Doing that would not only mean I end up with a lot of null columns where their not relevant, but also make an even bigger mess when it comes to add something else that might need to be linked to a path.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    So what you're really saying is;
    one document MAY have one package
    not
    one document HAS one package
    Right?
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2007
    Posts
    6
    No, documents and packages are not related in any way. A document MAY be related to a path, and a package MAY be related to a path. But a path MAY only be related to either one document OR one package, not one of each.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Method 1
    If the ids of both the documents and package tables are the same type then you could just have a common id field in the path table that could link to either a document or a package record, you'd also want a type field to indicate whether it is a document or a package. The disadvantage of this method is you couldn't use a FK and so there is a real danger of bad id's creeping into your link_id field unless carefully coded
    Code:
    create table Path (
       path_id
       ...
       link_type          -- this could be package or document
       link_id            -- this could be a package id or document id
    )
    Method 2
    MySQL version 5.0.2 has triggers so, if you're using this version, you could have a path id in both the documents and packages tables and the trigger could ensure that there is only one document or package that links to a given path.

    Mike

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Jak-S
    Ah, but documents and packages are completely different, with different columns etc.
    thanks for the clarification

    two tables, one for documents, one for packages

    path would be a non-key attribute in bofadem

    (i never pass up a chance to use one of my favourite words, bofadem)

    think about it this way: do you care enough about a path to keep track of it even if there is neither a document nor a package associated with that path?

    didn't think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2007
    Posts
    6
    OK, thanks guys, I'll give it some thought.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    Method 1
    If the ids of both the documents and package tables are the same type then you could just have a common id field in the path table that could link to either a document or a package record, you'd also want a type field to indicate whether it is a document or a package. The disadvantage of this method is you couldn't use a FK and so there is a real danger of bad id's creeping into your link_id field unless carefully coded
    I will resist the obvious To maintain RI in this sort of design I would not have the link_id in the table. This is the sort of circumstance where I would consider using a super key (path_id, link_type) in a foreign key relationship with check constraints on link_type in the package and document tables. Advantages: Declarative RI maintained. No complex code\ reliance on triggers\ sprocs. A path cannot be associated with two packages, two documents nor one of each. Disadvantages: arguably a bit naughty. May be considered a sledgehammer to crack a nut compared to, for example, Rudy's idea.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DELETED my post - I was saying that I wouldn't use the above afterall. I withdraw that now.
    Last edited by pootle flump; 08-17-07 at 04:17.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •