Hello - relative noob trying to resolve a 'many to many' relationships situation with either two or one look up tables. I have a hierarchical database Cat, Type and Prod. I can see doing a look up table between Cat.id and Type.id is a good move AND likewise creating a lookup table between Type.id and Prod.id (always with primary keys assigned on the columns)... My questions are: 1.Can I combine the two tables to make a single 3-column lookup table? 2.Can each of these three columns have Primary Key status? and 3.Providing I can code this, is this an efficient or really baaad way of solving the relationship lookup?
Only one primary key. But you can have any number of additional keys. You can keep the tables separate and then create a view to facilitate hierarchical lookups.
Only one primary per table is allowed. You can have other unique or non-unique keys per table. The best way to maintain proper relationships is using foreign keys. The requires that the value in one row of a table must match the value in another row of another table. You can use joins to lookup across 3 tables. An Inner Join sounds like the best join for what you are needing to do.
you need to do some reading on database normalization but you're getting there. very short answer is.. yes you can easily combine the 2 tables to make a single 3 column table - but only do it if you *always* have all 3 items of information. as for the "3 primary keys" - you don't need 3 primary keys - you need 1 unique index over the 3 columns which will ensure data integrity at db level and give you that lookup [this can also be accomplished by simply setting the primary key to be all 3 columns]