Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Does this ternary relationship need to be resolved?

    What I have are four tables; report, participation, person and vehicle. The participation table is a join between the other three. The cardinality between the report table and the person and vehicle tables is 0:M. These two tables have optional participation.

    I've created the tables and dumped sample data in them and they seem to give me exactly what I am looking for. The only caveat is that I have had to enter a single row of "N/A" in both the person and vehicle tables to satisfy the FK constraint in the "participation" table. It doesn't seem optimal but I'm not sure how else to handle this. Could someone please review the pic I have included and advise?

    Thanks,
    Frank
    Attached Thumbnails Attached Thumbnails export.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Seems your problem involves enforcing a unique constraint across nullable columns.
    What is your database platform? In SQLSVR, for instance, you can enforce this constraint using an indexed view, rather than the participation table's primary key.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hi Blindman,

    Thanks for the hand. I'm using mysql for this.

    Quote Originally Posted by blindman
    you can enforce this constraint using an indexed view, rather than the participation table's primary key
    Unless I'm not following you, and I'm almost certain that's the case, what I am doing here is selecting a "dummy" row of data which is coming out of the party and/or vehicle table to satisfy the FK constraint(s) in the participation table. The participation table PK is made up of the FKs from the report, party and vehicle tables. I'm not sure I want to enforce the constraint as you say, but rather not enforce it. Am I misunderstanding?

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Here is the DDL. It may help.

    CREATE TABLE participates (
    report_seq int(11) NOT NULL,
    vin char(17) NOT NULL default '',
    state_id_no char(25) NOT NULL default '',
    PRIMARY KEY (report_seq,vin,state_id_no),
    KEY state_id_no (state_id_no),
    KEY vin (vin),
    CONSTRAINT participates_ibfk_1 FOREIGN KEY (report_seq) REFERENCES report (report_seq),
    CONSTRAINT participates_ibfk_2 FOREIGN KEY (state_id_no) REFERENCES party (state_id_no),
    CONSTRAINT participates_ibfk_3 FOREIGN KEY (vin) REFERENCES vehicle (vin)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Always willing to help a fellow noodly one.
    But I don't understand what you mean when you say you don't want to enforce that constraint. If this is the case, then don't set those three columns as the primary key.
    Should a given combination of Report/Vehicle/Person be allowed more than once?
    Should a report be allowed to reference a vehicle more than once, if each reference specifies a different person?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not sure I'm following either, but do you want to do something like this:
    Implementing Table Interfaces - SQLTeam.com
    (ignore the "this is kind of like object oriented principles in databases" overtones)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803


    The idea behind the participation table is to associate the report to either one or both of the other entities. (when they participate)

    If we take the constraint between party and participation table for example...

    If I have a vehicle to enter into the vehicle table, I want that vehicle associated with the report. Let's say that there is no party to go along with the report. If you look at my PKs in the participation table (which really is nothing more than a join table) I have the PK from the party table in there as well, so when I enter data for only the vehicle, the PK from the party table is expecting a value as well, so when I say that I don't want to enforce a constraint, that's what I mean.

    Where I am running into problems is when there is data to enter for either the party OR the vehicle. These are both optional constraints because they will not always participate.

    If I remove the PKs from the party and vehicle relationships and leave only the report PK, then I would only be able to enter a single report, vehicle and party and that isn't correct because the relationship is 0:M.

    *Lightbulb goes on* I could however remove the PKs from the vehicle and party relationships as you suggest and add an identity column. I could then make the party and vehicle relationships nullable and still be able to enter more than one party and vehicle. Am I correct?

    I hope I'm a little clearer, it's been a long night.

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by pootle flump
    I'm not sure I'm following either, but do you want to do something like this:
    Implementing Table Interfaces - SQLTeam.com
    (ignore the "this is kind of like object oriented principles in databases" overtones)?
    Heya Poots. Thanks for the link. That's exactly what I want to do however, mysql won't do check constraints.

    I know I tend to over explain things sometimes but all I am trying to do is satisfy one or both of the FK constraints in the participation table. The vehicle and party tables can optionally participate in the relationship. The way I have it now, I am forced to enter a value for both tables.

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Well, I think Blindman was right on the money. Here's what I did.

    I left the PK on report_seq, removed the PKs from vin and state_id_no. I made both of these columns nullable. I added an identity column and a normal index and it works perfectly.

    At least I no longer have to enter dummy data to get my data in. Does this sound ok?

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Frunkie
    Well, I think Blindman was right on the money. Here's what I did.

    I left the PK on report_seq, removed the PKs from vin and state_id_no. I made both of these columns nullable. I added an identity column and a normal index and it works perfectly.
    In your original schema report_seq was not unique. Now you are saying it is. But if it is unique then I don't understand what you think you are gaining by adding an IDENTITY column.

    What I would suggest is that you create separate tables for what are apparently different subclasses of "partcicipation": One with Party, one with Vehicle and one with both. That way you can enforce whatever uniqueness constraints you require. I'm still not clear exactly what all the keys are but by creating the extra table(s) you ensure that they can all be enforced.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i agree with dportas, the issue of uniqueness of the chosen PK(s) is very much in question

    tip: when you think that applying a surrogate key (e.g. auto_increment) solves your modelling problem (by modelling, i mean the selection of PKs and FKs, nullability, uniqueness, and so on), you can be almost certain that what you are doing is simply masking the underlying unresolved issues

    please show your new DDL after the changes you spoke of

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by dportas
    In your original schema report_seq was not unique. Now you are saying it is. But if it is unique then I don't understand what you think you are gaining by adding an IDENTITY column.
    report_seq is a unique number within the report table. In the participation table, it's used it as part of a compound PK.

    Quote Originally Posted by dportas
    What I would suggest is that you create separate tables for what are apparently different subclasses of "partcicipation": One with Party, one with Vehicle and one with both. That way you can enforce whatever uniqueness constraints you require.
    dportas, could you please show me what you mean by this? I'm sorry but I don't understand.

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The best way to define the problem clearly and avoid any confusion is to post your CREATE TABLE statements (including all the keys please). I think that's what we need in order to help you further.

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    i agree with dportas, the issue of uniqueness of the chosen PK(s) is very much in question

    tip: when you think that applying a surrogate key (e.g. auto_increment) solves your modelling problem (by modelling, i mean the selection of PKs and FKs, nullability, uniqueness, and so on), you can be almost certain that what you are doing is simply masking the underlying unresolved issues

    please show your new DDL after the changes you spoke of

    Wow. I thought I had a kwel design going on. Thanks for the tip Rudy, those are words to live by.

    Here's the DDL after I made the changes.
    Code:
    CREATE TABLE participates (
      report_seq int(11) NOT NULL,
      vin char(17) default '',
      state_id_no char(25) default '',
      id int(11) NOT NULL auto_increment,
      PRIMARY KEY  (report_seq,id),
      KEY state_id_no (state_id_no),
      KEY vin (vin),
      KEY id (id),
      CONSTRAINT participates_ibfk_1 FOREIGN KEY (report_seq) REFERENCES report (report_seq),
      CONSTRAINT participates_ibfk_2 FOREIGN KEY (state_id_no) REFERENCES party (state_id_no),
      CONSTRAINT participates_ibfk_3 FOREIGN KEY (vin) REFERENCES vehicle (vin)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE party (
      fname char(30) NOT NULL,
      lname char(40) NOT NULL,
      dob date default NULL,
      issue_state char(2) NOT NULL,
      state_id_no char(25) NOT NULL,
      PRIMARY KEY  (issue_state,state_id_no),
      UNIQUE KEY Alter_Key2 (state_id_no)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE vehicle (
      mfg char(20) NOT NULL,
      mfg_year year(4) NOT NULL,
      model char(20) NOT NULL,
      color char(15) NOT NULL,
      action char(20) NOT NULL,
      notes text,
      vin char(17) NOT NULL,
      PRIMARY KEY  (vin),
      UNIQUE KEY vin (vin)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    I restructured the tables as dportas suggested. In the new pic you can see that each child table is dependant upon the report table. This schema doesn't work for me though. Please tell me if this is what everyone thinks I should do, then I'll explain why it won't work.

    EDIT: I posted the DDL from the other two tables as well. Just a side note: I didn't alter these tables in any way. All of my changes have been to the participation table.
    Attached Thumbnails Attached Thumbnails export.jpg  
    Last edited by Frunkie; 04-14-09 at 20:32.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here's where i am in catching up to you

    there is a report table, which has report_seq as it primary key

    so report_seq identifies (there's that word again, eh) a unique report

    now we have a "participation" table, which i don't fully understand yet, but which has report_seq as a foreign key

    the only possibilities, as seen from the point of view of the participation table, then, are that each report has one participation row, or each report has multiple participation rows

    with me so far?

    and now it seems to me that whatever it is that might require multiple participation rows for a single report, would involve multiple parties, or multiple vehicles, or multiple combinations of parties and people

    have i got that right so far?

    if this is correct, could you please give an example of a report with multiples of one or the other or both, and what this "participation" then actually means

    thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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