none
tabel issue / key /referential integrity RRS feed

  • Question

  • i have a tabel with parties

    in another table "transport" with transport records  concerning the parties

    the tables are related with 1 to many relation from parties to transport

    the relation has referential integrity

    now there is some transport that isn't related to any party , but it is about the same cars, so i like it in the same table.

    is it a good idea to give up referential integrity or should it be in another table ?

    Saturday, May 7, 2016 3:37 PM

Answers

  • Hi tekoko10,

    you have mentioned above that you don't want to define "not related party". so here I think it is better if you make a different table for it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by tekoko10 Monday, May 9, 2016 12:19 PM
    Monday, May 9, 2016 3:13 AM
    Moderator
  • I'm not sure I understand your data model correctly, but it looks like a Transport is a vehicle trip, using a single Car and driven by a single Chauffeur.

    Can that Transport include more than one Party? If so I think you would need an additional TransportParty table that allowed that single Transport to carry multiple Parties. In the case of a Transport without any passengers, there would not be any TransportParty rows referring to that particular Transport.

    If a Transport can only have zero or one Party, then you just need to allow Nulls for the foreign key Party attribute in the Transport table. A Transport could then be entered without a Party, and referential integrity is still enforced. Integrity only prevents entering a Party that does not exist, but does not require that every Transport have a party.


    Paul

    • Marked as answer by tekoko10 Tuesday, May 10, 2016 2:46 PM
    Monday, May 9, 2016 10:10 PM

All replies

  • is it a good idea to give up referential integrity?

    Absolutely not.  From the very limited information you've provided, my first impression would be that the relationship type should be modelled by a third table which references the parties and transport tables.  It is sometimes not realized that a unary relationship type can be modelled by a table, just as a binary, ternary etc relationship type can be.  Whether this would be appropriate in your case is impossible to say, as you have provided insufficient information to be able to judge.

    If you can provide a detailed description of the real world situation being modelled, in terms of the real world entity types and the relationship types between them, the dog would have a better chance of seeing the rabbit.


    Ken Sheridan, Stafford, England

    Saturday, May 7, 2016 5:49 PM
  • now there is some transport that isn't related to any party , but it is about the same cars, so i like it in the same table.

    is it a good idea to give up referential integrity or should it be in another table ?

    Hi tekoko,

    You could define a "not-related-party" party, where all the not related transports are related to.

    Imb.

    Saturday, May 7, 2016 9:13 PM
  • Imb-hb , yes, that works of course , but i taught it is not so elegant
    Sunday, May 8, 2016 5:31 AM
  • Mr Ken, maybe this helps

    i want records in the transport tabel that are not related to the parties

    Sunday, May 8, 2016 7:01 AM
  • At present the Transport table is modelling a ternary relationship type between Parties, Cars and Chauffeurs.  Do you now wish to model a binary relationship type between Cars and Chauffeurs, or a relationship type between Cars and some other entity type?

    I can't answer your question because you have not done what I asked in my earlier post and described 'the real world situation being modelled, in terms of the real world entity types and the relationship types between them'.  The key words here are 'real world'.  

    There are essentially three stages in designing the schema for a relational database:  

    1.  The conceptual model:  this describes the real world entity types for which the database is to provide a physical model.

    2.  The logical model:  this describes the reality described by the conceptual model in terms of entity types and how they relate to each other.  There will not necessarily be only one logical model for any given reality.

    3.  The physical model:  this is the expression of whichever logical model is used from those which give a valid description of the conceptual model in terms of the tables and the relationship types between them.

    In summary, and in very simplified terms, a physical model is a physical representation of a logical model, which is a logical representation of a conceptual model.

    What you have shown us is the physical model.  This gives us some clues as to the nature of the logical and conceptual models, and it would be very easy to give you an answer to your question by second guessing the details of these, but, in the absence of a more detailed understanding of the reality being modelled, the guesses and the answer might well be incorrect and lead you astray.  In the absence of further details from you therefore, I would not wish to hazard an uninformed guess.

    Ken Sheridan, Stafford, England

    Sunday, May 8, 2016 11:26 AM
  • Hi tekoko10,

    you have mentioned above that you don't want to define "not related party". so here I think it is better if you make a different table for it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by tekoko10 Monday, May 9, 2016 12:19 PM
    Monday, May 9, 2016 3:13 AM
    Moderator
  • I'm not sure I understand your data model correctly, but it looks like a Transport is a vehicle trip, using a single Car and driven by a single Chauffeur.

    Can that Transport include more than one Party? If so I think you would need an additional TransportParty table that allowed that single Transport to carry multiple Parties. In the case of a Transport without any passengers, there would not be any TransportParty rows referring to that particular Transport.

    If a Transport can only have zero or one Party, then you just need to allow Nulls for the foreign key Party attribute in the Transport table. A Transport could then be entered without a Party, and referential integrity is still enforced. Integrity only prevents entering a Party that does not exist, but does not require that every Transport have a party.


    Paul

    • Marked as answer by tekoko10 Tuesday, May 10, 2016 2:46 PM
    Monday, May 9, 2016 10:10 PM
  • Paul , that's it , i didn't realize the key could be null , many thanks !
    Tuesday, May 10, 2016 2:47 PM
  • Paul , that's it , i didn't realize the key could be null , many thanks !

    Hi tekoko,

    The problem with a party with a Null value is that you cannot distinguish any more between not filled in by purpose and not filled in by accident.

    In those cases I find it "more elegant" to use a "not defined party".

    Imb.

    Tuesday, May 10, 2016 4:26 PM