November 28, 2012 at 2:38 pm
I have a User table and Address table, then a cross reference table to tie the two together. The addresses won't be reused across Users, but this allows me to have multiple address records per user. Also it keeps the address info in a central table so User, Business, Vendor, Collateral, etc is all using the same Address table.
So here is a very basic mock-up of the table layouts:
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'xrUserAddress' AND type_desc = 'USER_TABLE') DROP TABLE dbo.[xrUserAddress]
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'User' AND type_desc = 'USER_TABLE') DROP TABLE dbo.[User]
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'Address' AND type_desc = 'USER_TABLE') DROP TABLE dbo.[Address]
Go
CREATE TABLE dbo.[User] (UserID INTEGER IDENTITY(1,1) NOT NULL, [Name] NVARCHAR(50));
ALTER TABLE dbo.[User] ADD CONSTRAINT [PK_User] PRIMARY KEY ([UserID]) ON [PRIMARY];
GO
CREATE TABLE dbo.[Address] (AddressID INTEGER IDENTITY(1,1) NOT NULL, [Address] NVARCHAR(50));
ALTER TABLE dbo.[Address] ADD CONSTRAINT [PK_Address] PRIMARY KEY ([AddressID]) ON [PRIMARY];
GO
CREATE TABLE dbo.[xrUserAddress] (UserID INTEGER NOT Null, AddressID INTEGER NOT Null);
ALTER TABLE dbo.[xrUserAddress] ADD CONSTRAINT [PK_xrUserAddress] PRIMARY KEY CLUSTERED (UserID, AddressID) ON [PRIMARY];
ALTER TABLE dbo.[xrUserAddress] ADD CONSTRAINT [FK_xrUserAddress_UserID] FOREIGN KEY (UserID) REFERENCES dbo.[User] (UserID) ON DELETE CASCADE;
ALTER TABLE dbo.[xrUserAddress] ADD CONSTRAINT [FK_xrUserAddress_AddressID] FOREIGN KEY (AddressID) REFERENCES dbo.[Address] (AddressID) ON DELETE CASCADE;
GO
INSERT INTO dbo.[User] (Name) VALUES ('George Washington'),('Bill Clinton'),('Ronald Reagan')
INSERT INTO dbo.[Address] ([Address]) VALUES ('Westmoreland, Virginia'),('Little Rock, Arkansas'),('Tampico, Illinois')
INSERT INTO dbo.[xrUserAddress] (UserID,AddressID) VALUES (1,1),(2,2),(3,3)
Go
Note these tables are VERY basic, our tables are extensively more complex, rather this is just an example to show how I'm basically setting-up the constraints between the three tables in our environment.
Using this scenario if I delete User 1 from dbo.[User] it will delete the row in dbo.[xrUserAddress], but this now leaves an orphaned address record in dbo.[Address]. The idea behind setting-up tables like this is I can have multiple addresses per User, but can someone suggest a better way of doing it so I don't have to use two deletes when I'm removing a User? Is there something that'll cascade a delete up so that the PK is dropped if the FK is dropped? Or would a Delete Trigger be the best option here?
Thanks for any suggestions.
November 28, 2012 at 2:54 pm
Quick update on this, I went ahead and rolled out Delete Triggers to satisfy this, but if someone has any other suggestions please let me know.
Thanks.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply