January 15, 2008 at 1:38 pm
I try to find the best design for a SQL Server 2005 database structure.
I have the following 'objects':
Facilities
Departments - 'dependents' of a Facility
Users
Addresses - each of the above can have none or more Addresses. No need to reuse an Address i.e. one Address is used only for its 'object'.
The basic tables design would be:
CREATE TABLE [dbo].[Facility](
[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
[cDepartmentID] [nvarchar](3) NOT NULL,
[cDepartmentName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
[cUserName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Addresses](
[pkAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[cAddress] [nvarchar](255) NULL,
[cZip] [nvarchar](5) NULL,
[cEmail] [nvarchar](255) NULL
)
GO
The question regards the Addresses table:
How can I design the Addresses table(s) so I can enforce relational integrity and cascade update and delete for each Facility, Department and User tables?
I figured out the following options, but none achieves my requirements:
1. An Address table for each 'object': FacilityAddress, DepartmentAddress, UserAddress with the corresponding foreign key in each Address table. This solution duplicates all the addresses fields (which are more than I showed in the example) and will be an error-prone solution to support on a real-life, long time, complex application.
2. A common Addresses table with a discriminator field (1=Facility, 2=Department, 3=User). This doesn't allow me to enforce referential integrity with constraint nor the cascading, maybe only with triggers.
3. A common Addresses table with a foreign key for each 'object (fkFacility, fkDepartment, fkUser) allowing nulls. In this case, the 'dependence' between Facility and Department stops me to enforce cascade deletes/updates on both Facility and Department foreign key (circular...).
4. An intermediate (link) table between each 'object' and Addresses table with the foreign keys (Facility, Addresses). The ‘link’ table would be like:
CREATE TABLE [dbo].[FacilityAddress](
[pkFacilityAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES Facility (cFacilityID) ON UPDATE CASCADE ON DELETE CASCADE,
[fkAddress] [int] NOT NULL FOREIGN KEY REFERENCES Addresses (pkAddress) ON UPDATE CASCADE ON DELETE CASCADE,
)
This case enforces cascade delete between Facility and FacilityAddress, but not between FacilityAddress and Addresses.
What I want is when I delete a Facility to get the corresponding Addresses records deleted. Same for Department, etc.
So, is there a method to design such structure?
PS. Please keep in mind that this is just a simplified example (reduced at the key information) of my real structure – which is way more complicated.
Thanks,
Ioan
January 16, 2008 at 6:57 am
I'd use the 4th option and turn on cascading deletes from there (if you really want to use those things). However, what happens if an address is used more than once? Two of the faculty live at the same address (husband & wife), then one leaves (divorce), you can't simply delete the address because one left. That's why I hate cascading deletes. Too simple-minded.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 7:20 am
Grant Fritchey (1/16/2008)
However, what happens if an address is used more than once? Two of the faculty live at the same address (husband & wife), then one leaves (divorce), you can't simply delete the address because one left.
One Address is used only once, that's for sure, the example you gave does not apply in my case (no divorce allowed :)).
Grant Fritchey (1/16/2008)
That's why I hate cascading deletes. Too simple-minded.
The cascade delete enforces the data integrity, else has to be handled 'manually'. So, the 'simple-minded' way is the relational-db way.
Grant Fritchey (1/16/2008)
I'd use the 4th option and turn on cascading deletes from there (if you really want to use those things).
I don't understand what you mean by 'turn on cascading deletes' in the 4th case. I did, is set, but it works between Facility and FacilityAddress (deleting a Facility deletes the related FacilityAddresses) and between Addresses and FacilityAddresses (deleting an Address deletes the related FacilityAddresses), but to from Facility, through FacilityAddress all the way to Addresses (deleting a Facility has to delete the related Addresses).
Thanks for your comments.
Ioan
January 16, 2008 at 9:50 am
I don't understand one thing. You said 'Address' could be used for facility, department of user, so why did you have foreign key to facility table only? Also can one address belong to more than 1 table for example it can belong to facility and user at the same time?
I will be very cautious to use cascade delete. It is good but you have to make sure the two tables are in one-to-one relationship.
January 16, 2008 at 10:00 am
Loner (1/16/2008)
I don't understand one thing. You said 'Address' could be used for facility, department of user, so why did you have foreign key to facility table only?
I used (only) Facility for example, but Department and User should work same way.
Loner (1/16/2008)
Also can one address belong to more than 1 table for example it can belong to facility and user at the same time?
No. One Address belongs only to one of the 'objects': Facility or Department or User (exclusive 'OR').
Loner (1/16/2008)
I will be very cautious to use cascade delete. It is good but you have to make sure the two tables are in one-to-one relationship.
They are, it's the requirement, so no problem for this. (Actually is an 1-to-many because I can have for a Facility - for example - more than one address, with another field as discriminator - I simplified the case).
Thanks for your comments.
Ioan
January 16, 2008 at 10:06 am
It doesn't keep cascading, since cascades flow from parent to child. Since FacilityAddress is a "child" to BOTH Address and to Facility, it's not going to keep cascading, since it's got nowhere to go.
Now - if you were talking about another table to which FacilityAddress was the Parent (like as a wild example - FacilityAddressTenant for example), and you wanted cascade delete, then in that case, deleting the facility would wipe the facility addresses and all of the tenants attached to them...
Use #4 with a cascade, and a job that occasionally goes through and cleans out unused addresses (preferrably ones that haven't been used in a little while, so it's not too aggressive and prevents you from addiing new addresses.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 10:12 am
Matt Miller (1/16/2008)
It doesn't keep cascading, since cascades flow from parent to child. Since FacilityAddress is a "child" to BOTH Address and to Facility, it's not going to keep cascading, since it's got nowhere to go.
Exactly, this the problem in this design.
Matt Miller (1/16/2008)
Use #4 with a cascade, and a job that occasionally goes through and cleans out unused addresses (preferrably ones that haven't been used in a little while, so it's not too aggressive and prevents you from addiing new addresses.
This could be a solution, I will give it a tough.
Thanks for your comments.
Ioan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply