May 15, 2006 at 1:47 pm
I have two tables, Clients and Locations that I'm trying to relate. Each client can have one or more locations--one of which is the primary location. The tables are similar to the following:
Clients As c
================
ID
Name
PrimaryLocationID
Locations As l
================
ID
City
ClientID
I want to be able to insert the first location at the same time as the client but c.PrimaryLocationID has a foreign key constraint with l.ID and l.ClientID has a foreign key constraint to c.ID.
So my question is, "Should I temporarily disable constraints within the confines of a transactional stored procedure for inserts or is there a better way to design the schema?"
Any help would be appreciated.
May 15, 2006 at 2:22 pm
You have two logical entities : Client and Location. These two have a many-to-many relationship between them since a client can have 1-N locations and a location can be serviced by 1-N clients.
When you create a physical structure, you will end up with three tables:
CLIENT, CLIENT_LOCATION, LOCATION
CLIENT : ID, NAME (ID being the PK)
LOCATION : ID, CITY (ID being the PK)
CLIENT_LOCATION : CLIENT_ID, LOCATION_ID, IS_PRIMARY (Combination of CLIENT_ID and LOCAITON_ID being the PK). The IS_PRIMARY flag will be Y for one location per client/location combination.
Hth
May 15, 2006 at 2:33 pm
Thank you for your reply. Obviously I need to add some clarification to my example. Locations belong to only 1 client. Perhaps the table should have looked like this:
Locations As l
================
ID
Address 1
Address 2
Phone
ClientID
May 15, 2006 at 3:22 pm
Ok - if that is the case, then you do not need to have LocationID in the CLIENT table. The relationship is 1:N then instead of M:N. So, you will then have:
CLIENT: ID (PK), NAME
LOCATION : ID (PK), CITY, CLIENT_ID (FK TO CLIENT TABLE), IS_PRIMARY.
One Client can have multiple locations but a location can belong to only one client. That way, in your transaction, when you insert, you can insert into the client table and then create the location and update the FK link as well.
May 15, 2006 at 3:24 pm
If each location is limited to 1 client, you could put your Primary location identifier into the location table instead of the client table.
May 15, 2006 at 3:25 pm
Sorry about the simulpost, rsharma. It wasn't there when I previewed, but was when I posted.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply