Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schema design for immutable FK data vs user created FK data Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 6:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 PM
Points: 43, Visits: 111
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
Post #1540516
Posted Wednesday, February 12, 2014 9:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 346, Visits: 3,352
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.
Post #1540843
Posted Wednesday, February 12, 2014 8:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 PM
Points: 43, Visits: 111
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.
Post #1541011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse