Composite Foreign Key in Database Diagram

[Lc3]

Hi, I'm designing a MySQL database. I have an entity with a composite primary key, and I'm referencing this entiry in another so I need to use a composite foreign key, e.g.

 

KEY `fk` (`site_name`,`company_name`),
CONSTRAINT `fk`
    FOREIGN KEY (`site_name`, `company_name`)
    REFERENCES `site` (`site_name`, `company_name`)

 

How is such a relationship represented in a Database Diagram? I haven't seen any examples of this. Is it unusual design?

[448191]

In terms of how the one entity relates to the other it's just a 1:1 relation. I would really recommend you use an ID instead of a text PK, let alone 2, for a whole scala of reasons.

[Lc3]

In terms of how the one entity relates to the other it's just a 1:1 relation. I would really recommend you use an ID instead of a text PK, let alone 2, for a whole scala of reasons.

 

actually it's a 1:M not 1:1 because the table with the foreign keys has primary keys

product_barcode, site_name, company_name

as it's PK, the table with the composite primary key was

site_name, company_name

there were going to be many products on one site...

 

... but that's an aside anyway I didn't give oyu enough information to know that so it's not important, in regards to what you were actually suggesting (that I should have a surrogate key), i have heard that using a combination of natural attributes has it's advantages. How do I know when to use a surrogate and when not to?

[448191]

There are only very few cases where I would recommend a natural/meaningful key. I personally would not use a composite primary key for anything other than intersection tables. A surrogate key outperforms a text based key and gives greater flexibility (what if I misspelled my company name?).

[Lc3]

A surrogate key outperforms a text based key and gives greater flexibility (what if I misspelled my company name?).

 

There's always cascade updates

 

I personally would not use a composite primary key for anything other than intersection tables.

 

you probably have a point though i'll have to think about it.