Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    (Template + Hierarcy) vs. (Separate Tables)

    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).

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    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)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2008
    Posts
    4
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yes, you should still have two tables.
    One is storing templates, the other is storing instances. So there really is no duplication.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •