Bridge table or composite key?
I am trying to decide whether to use some sort of composite keys or a bridge table. Not sure whether one relationship is many to many or not.
table name- main
Primary key column - code
name column
address column
Names can be repeated, but only with a different address. An address can be repeated but only with a different name. I've used bridge tables before but only between tables not two columns. Do I need composite keys instead that are separate from the primary key??
table name- main
Primary key column - code
name column
address column
Names can be repeated, but only with a different address. An address can be repeated but only with a different name. I've used bridge tables before but only between tables not two columns. Do I need composite keys instead that are separate from the primary key??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Name is actually a Restaurant name, So it has only one admin name per restaurant. As far as not seeing a bridge table, I did not create one yet and that is the nature of the question.
Restaurants? Why to make it so complex? It seems you need just table of Restaurants… You don't need separate table for names. Separate table for addresses is just icing on the cake in this case but you may implement it.
So
RestaurantID integer -- Primary key, possibly autogenerated
RestaurantName varchar(100) --
RestaurantAddressID integer -- FK in Addresses table
And if you would like to ensure Name and Address unicity then create unique index on RestaurantAddressID + RestaurantName. BUT remember this index or any other "name unicity" implementation does not prevent you from typos in names so the two records will represent one restaurant in fact.
So
RestaurantID integer -- Primary key, possibly autogenerated
RestaurantName varchar(100) --
RestaurantAddressID integer -- FK in Addresses table
And if you would like to ensure Name and Address unicity then create unique index on RestaurantAddressID + RestaurantName. BUT remember this index or any other "name unicity" implementation does not prevent you from typos in names so the two records will represent one restaurant in fact.
<<Restaurants?>>
You need to describe what it is your trying to model.
Jim.
You need to describe what it is your trying to model.
Jim.
You do need a bridge or intersection table (depending on the specific type of model). [Btw, do a logical data model first, don't go straight to tables. Sorry, had to say that, though I suspect you may ignore it.]
Because you will presumably store other attributes (columns), you will need a names table, an addresses table, and a table that combines the two. Otherwise, you will violate 2NF because some of the other columns will not relate to the entire key. You will very likely end up adding some "child" tables as well.
When creating the multiple tables, you do not need an artificial key (such as an identity / autonumber) on the bridge / intersection table(s).
Instead, use the keys of the parent table(s), along with the child key [unless you have, say, more than 4 natural key columns, then consider a single, artificial key].
Because you will presumably store other attributes (columns), you will need a names table, an addresses table, and a table that combines the two. Otherwise, you will violate 2NF because some of the other columns will not relate to the entire key. You will very likely end up adding some "child" tables as well.
When creating the multiple tables, you do not need an artificial key (such as an identity / autonumber) on the bridge / intersection table(s).
Instead, use the keys of the parent table(s), along with the child key [unless you have, say, more than 4 natural key columns, then consider a single, artificial key].
To help you should tell more about the data model. I don't see any bridge table in your design. What is the purpose of the main table? Why would you use bridge table to some names? What is the meaning of the code PK? Etc. etc.
So before you design some data model you have to define what data you would like to collect/maintain. Then you may decide about the table structures which should reflect the data meaning. Then you may establish some rules which are describing the reality the "address can be repeated but only with a different name" does not seem to be real requirement at this point.