We help IT Professionals succeed at work.

Bridge table or composite key?

Arnold Layne
Arnold Layne used Ask the Experts™
on
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??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
I would go with a table design like this
ID Autonumber
NameID Number PK
AddressID Number PK
Does your model mean two equally named people cannot have equal address? In other words father and son cannot share one family house?

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.
Arnold LayneDeveloper

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Restaurants?>>

You need to describe what it is your trying to model.

Jim.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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].