February 11, 2014 at 6:25 pm
Hi all,
Just after recommendations on the best way to design a foreign key table that, for example, defines a type, which contains immutable data in addition to types that are user defined.
E.g. without any sort of protection:
CREATE TABLE [dbo].[MyTestFK]
(
[MyTestFKID] [int] IDENTITY(1,1) NOT NULL,
[Type] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_MyTestFK] PRIMARY KEY CLUSTERED
(
[MyTestFKID] ASC
)
)
GO
CREATE TABLE [dbo].[MyTestData]
(
[MyTestDataID] [int] IDENTITY(1,1) NOT NULL,
[MyTestFKID] [int] NOT NULL,
CONSTRAINT [PK_MyTestData] PRIMARY KEY CLUSTERED
(
[MyTestDataID] ASC
)
)
GO
ALTER TABLE [dbo].[MyTestData]
ADD CONSTRAINT [FK_MyTestData_MyTestFK] FOREIGN KEY([MyTestFKID])
REFERENCES [dbo].[MyTestFK] ([MyTestFKID])
GO
INSERT INTO [dbo].[MyTestFK] ([Type])
VALUES (N'ImmutableType1')
INSERT INTO [dbo].[MyTestFK] ([Type])
VALUES (N'UserType1')
GO
Basically, one proposal (which I am not convinced is the way to go) is to structurally separate immutable and user types into separate tables. Is there a "best practices" for this scenario?
Thanks,
SC
February 12, 2014 at 9:15 am
If the only difference is that some rows may have certain data elements updated and others can't, I'd say they belong in the same table (there are bound to be exceptions, but I can't think of an example).
Say you have a business rule that says element X is one of those which can (or can't) be renamed? Then someone high up changes their mind on that If they're all in one table, you have a flag which identifies this. You change it. Job done.
Now, think about the pain you've got if you have two tables and you have to switch the data from one to the other, updating all dependencies to keep referential integrity.
That could get painful by comparison.
Basically, if you see a fk which references two tables at the same time ... how does that work? It doesn't, if it's in one table it can't be in the other, so all your inserts to the child table fail.
If anyone replies to this with "Well you could triggers .. ", please just shoot them and do us all a favour. They're just trying to do something stupid to allow themselves to do something stupid.
If they suggest dummies in each table. Shoot them twice.
hth
Edit for clarity on one point
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 12, 2014 at 8:52 pm
andrew gothard (2/12/2014)
Basically, if you see a fk which references two tables at the same time ... how does that work? It doesn't, if it's in one table it can't be in the other, so all your inserts to the child table fail.
Yes I agree there. I think they were looking at applying a constraint rather than a FK but my opinion is that you'd want a single FK in a relational database rather than some custom key constraint.
andrew gothard (2/12/2014)If anyone replies to this with "Well you could triggers .. ", please just shoot them and do us all a favour. They're just trying to do something stupid to allow themselves to do something stupid.
Yes agreed there too.
It hasn't been done yet so let's see how it pans out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply