1

Please see the following example Database Model i made to help visualize my problem:

enter image description here

In this example, the relationship between car and history is 1 to zero or more and relationship between car and options is 1 to zero or more.

both tables have car car_id as foreign key but Visio also wants customer table primary key for each of these tables as foreign key as seen in the diagram above. I am not sure if that's necessary/correct. if i remove the added foreign keys , visio marks the relationship as non-identifiable (dotted line) so i feel as though i am doing something wrong

would anyone be able to tell me why Visio is adding the customer cust_id to child tables of car? and whether or not removing the Foreign Keys manually is bad idea in this case?

Updated Diagram based on comments (and selected answer)

enter image description here

Community
  • 1
  • 1
ke3pup
  • 1,835
  • 4
  • 36
  • 66

1 Answers1

1

There is nothing "wrong" with non-identifying relationship per-se.

Is car_id unique on its own, or in the context of cust_id (so we can have same car_id for different cust_ids)?

  • If it's unique on its own, the customer-car relationship should be non-identifying.
  • Otherwise it should be identifying. A natural consequence of this is the propagation of cust_id to all "downstream" entities.

BTW, the same question could be asked about opt_id and hist_id - are they unique on their own or in the context of the car's PK? The answer determines whether car-options and car-history relationships should be identifying or not.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Thanks for your answer. Please correct me if i am wrong but in above example the `relationship between CAR and CUSTOMER` should be `non-identifiable` given either can exists independently. I made the change in the Diagram (See updated question) and effects are seen in "Downstream" tables as well. – ke3pup Apr 21 '12 at 11:22
  • 1
    @techventure This is not a matter of whether `car` can exist independently (it cannot, since you have a non-NULL FK to another table), but whether it can be _identified_ independently. Assuming it can, your new diagram looks OK. Of course, the same question can be asked for all other relationships (e.g. can `invoice` be identified with the `in_id` **alone**, etc...). – Branko Dimitrijevic Apr 21 '12 at 13:21