Lc3 Posted January 8, 2009 Share Posted January 8, 2009 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? Link to comment https://forums.phpfreaks.com/topic/139985-composite-foreign-key-in-database-diagram/ Share on other sites More sharing options...
448191 Posted January 8, 2009 Share Posted January 8, 2009 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. Link to comment https://forums.phpfreaks.com/topic/139985-composite-foreign-key-in-database-diagram/#findComment-732364 Share on other sites More sharing options...
Lc3 Posted January 8, 2009 Author Share Posted January 8, 2009 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? Link to comment https://forums.phpfreaks.com/topic/139985-composite-foreign-key-in-database-diagram/#findComment-732368 Share on other sites More sharing options...
448191 Posted January 8, 2009 Share Posted January 8, 2009 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?). Link to comment https://forums.phpfreaks.com/topic/139985-composite-foreign-key-in-database-diagram/#findComment-732378 Share on other sites More sharing options...
Lc3 Posted January 8, 2009 Author Share Posted January 8, 2009 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. Link to comment https://forums.phpfreaks.com/topic/139985-composite-foreign-key-in-database-diagram/#findComment-732381 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.