(Template + Hierarcy) vs. (Separate Tables)

mcwhelan · #1 (**permalink**) 04-23-08, 09:18

The title is probably a bit cryptic, so bear with me and I shall attempt explanation...

The situation that I am modelling is where the data is used in a fairly complex calculation and when the calculation is performed, the data used is kept frozen (a snapshot taken). The data itself is quite complicated with various tables and many-to-many relationships between them.

I'll give a cut-down example to illustrate the two scenarios I have for the database design. So, for simplification, just assume that there are two items of data used in the calculation, ItemA and ItemB (data types not important).

Scenario 1: (Template + Hierarchy)
One table, say tblCalc
CalcID (PK)
ItemA
ItemB
ParentID (self-referential FK)

Template defintions are where ParentID is Null

Pros: Concise.
Cons: Implies a hierarchy which does not really exist. There would only ever be 2 levels (template and version), but the data structure allows arbitrary levels.

Scenario 2: (Separate Tables)
tblCalcDefinition
DefnID (PK)
ItemA
ItemB

tblCalc (or CalcHistory or whatever)
CalcID
ItemA
ItemB
DefnID (FK to tblCalcDefinition)

Pros: describes the situation fairly precisely.
Cons: duplicated tables (columns would have to be kept in step). Makes the whole data structure more complicated.

What makes it more complicated is that I have many-to-manys between link tables, so to avoid having a potential conflict in the part of 2 inherited keys that comes from the same grandparent table (if you see what I mean), I have all intermediate link tables with composite primary keys rather than their own (identity) PK.

I am favouring scenario 1 for the simplicity, but I'm worrying that it may allow bad data to creep in. Am I being too fussy and unreasonable? Is there a third scenario that I have missed?

Thanks (and sorry for the long first post).

blindman · #2 (**permalink**) 04-23-08, 10:04

I think your attempt to simplify the scenario has only made it more ambiguous. The Devil is in the details, and you have not provided them.

mcwhelan · #3 (**permalink**) 04-23-08, 10:36

It's insanity that lies in the details.
Try this...

Scenario 1

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblThing](
	[ThingID] [int] IDENTITY(1,1) NOT NULL,
	[ThingName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ItemA] [int] NULL,
	[ItemB] [int] NULL,
 CONSTRAINT [PK_tblThing] PRIMARY KEY CLUSTERED 
(
	[ThingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[tblThingVersion](
	[VersionID] [int] IDENTITY(1,1) NOT NULL,
	[ThingID] [int] NULL,
	[ThingVersionName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ItemA] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ItemB] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblThingVersion] PRIMARY KEY CLUSTERED 
(
	[VersionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblThingVersion]  WITH CHECK ADD  CONSTRAINT [FK_tblThingVersion_tblThing] FOREIGN KEY([ThingID])
REFERENCES [dbo].[tblThing] ([ThingID])
GO
ALTER TABLE [dbo].[tblThingVersion] CHECK CONSTRAINT [FK_tblThingVersion_tblThing]
SET ANSI_PADDING OFF
GO


INSERT INTO tblThing (ThingName, ItemA, ItemB) VALUES ('This is a template',10,10)
INSERT INTO tblThingVersion (ThingID, ThingVersionName, ItemA, ItemB) VALUES (1,'Version 1',10,15)
INSERT INTO tblThingVersion (ThingID, ThingVersionName, ItemA, ItemB) VALUES (1,'Version 2',10,20)

Scenario 2

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblOtherThing](
	[ThingID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[ThingName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ItemA] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ItemB] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblOtherThing] PRIMARY KEY CLUSTERED 
(
	[ThingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tblOtherThing]  WITH CHECK ADD  CONSTRAINT [FK_tblOtherThing_tblOtherThing] FOREIGN KEY([ParentID])
REFERENCES [dbo].[tblOtherThing] ([ThingID])
GO
ALTER TABLE [dbo].[tblOtherThing] CHECK CONSTRAINT [FK_tblOtherThing_tblOtherThing]
GO


INSERT INTO tblOtherThing (ParentID, ThingName, ItemA, ItemB) VALUES (Null,'This is a template',10,10)
INSERT INTO tblOtherThing (ParentID, ThingName, ItemA, ItemB) VALUES (1,'Version 1',10,15)
INSERT INTO tblOtherThing (ParentID, ThingName, ItemA, ItemB) VALUES (1,'Version 2',10,20)

blindman · #4 (**permalink**) 04-23-08, 11:32

ItemA and ItemB are varchar(10) in one table and int in the other?

I'm thinking your first scenario is the correct implementation. Storing both things and thingversions in one table implies that they are identical entities. They are not.
You have two entities: Things, and Versions.
So, use two tables.

mcwhelan · #5 (**permalink**) 04-23-08, 11:54

Quote:

Originally Posted by blindman

ItemA and ItemB are varchar(10) in one table and int in the other?

That's just me being sloppy. The idea is that they are the same.

And of course I managed to switch Scenario 1 and Scenario 2 in my 2 explanations. Damn.

Quote:

Originally Posted by blindman

I'm thinking your first scenario is the correct implementation. Storing both things and thingversions in one table implies that they are identical entities. They are not.
You have two entities: Things, and Versions.
So, use two tables.

Well, that's what I thought. It's a correct model of the data and doesn't create a misleading hierarchy. But...
What if my "template" is a lot bigger, say I have ItemA, ItemB,..., ItemZ, all of which are both on Things and on Versions. Should I still have two (almost) identical tables?

Actually, the more I think about this, the more I think that it should still have 2 separate tables. I'm just being lazy.

Thanks.

blindman · #6 (**permalink**) 04-23-08, 12:04

Yes, you should still have two tables.
One is storing templates, the other is storing instances. So there really is no duplication.

Internet Services

Web Development

Digital Marketing

Consumer Tech