Design question with shared tables

  • I have a few entities that share the fields of a common entity.

    A door entity has a jamb and header that have its own finish, and the leafs of the door have their own finish. I am running into issues where i cannot cascade on delete for circular references.

    I have attached a screen shot of what I am working on, can someone point me in the right direction of the best way to design this type of scenario.

    The finish table is shared with about 8 other tables.

    The finish table does not have pre-defined data so it's not like a constraint table. The data is filled from a user selection on the client. In other words the finish is never determined until a user makes a selection from some other unknown source.

    Please see the attachment.

    Dam again!

  • How about some ddl and an explanation of what you are trying to do?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • my first suggestion is to change the names of the primary keys in the core tables themselves: DoorID, FinishID, and LeafID for example, based on your image you posted.

    you (in my opinion) correctly named the foreign key IDs, but for me, the column name in the Primary table needs to always be the exact same name in the child tables.

    if you can provide the DDL of the tables, a couple of sampel rows that show the issue, as well as the delete statement you are trying to do, we can help .

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I have attached a file with the DDL so you guys can create a little schema.

    I have also attached a screen shot of where the finish info is coming from, The color picker is primitive and is, for now, only to demonstrate that I am not supplying per-defined finish / color data to the user.

    The thing to note is that each horizontal, vertical, jamb, each piece of the elevation can be any color and the user building the elevation has much control over what they're building.

    In a perfect world it would be nice to have a finish datatype that could easily be mapped to my c# code, "Like I've got it below"

    You'll also notice the in my finish table that the fields left,right, top, bottom now should make since since a bay has a left,right,top,bottom, and a door has a left, right, top, but no bottom, and the leaf to the door has a left, right, top, bottom, ect..

    I have all the data packaged up perfectly coming from the client i just need for it to make since in the database as well. I am using the new webAPI in .net and MVC 4.5. This data comes from the client as JSON and maps perfectly into my classes.

    Below is a few classes that show how the finish object as a property to the bay object.

    public class Elevation

    {

    public int ID { get; set; }

    public String Name { get; set; }

    public Size Size { get; set; }

    public ICollection<Bay> Bays { get; set; }

    }

    public class Bay

    {

    public int ID { get; set; }

    public int Position { get; set; }

    public Siteline Siteline { get; set; }

    public Finish Finish { get; set; }

    public Size Size { get; set; }

    public Glass Glass { get; set; }

    public ICollection<Horizontal> Horizontals { get; set; }

    public Door Door { get; set; }

    }

    public class Finish

    {

    public string Left { get; set; }

    public string Right { get; set; }

    public string Top { get; set; }

    public string Bottom { get; set; }

    }

    Here is a link to a few demo videos that i posted on youtube last week. I am not here to try to promote anything, but if it can help you guys help me approach this correctly that makes since.

    http://www.youtube.com/user/storefrontsystem

    In a nutshell what is the best approach for my finish table entity, so that it makes since when mapping the data to my c# code that i've provided.

    Thank you very much! As always... 😉

    Dam again!

  • It would probably be better if you used reply instead of editing. when you edit nobody gets a notification, but when you add a new reply they do. 😛

    Thanks for the ddl, unfortunately it is incomplete. There is nothing for the table Finish which is referenced by at least one of the foreign key constraints. As a result this code will not run.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ok, I've scripted out a few tables and each one of these need to have one finish association for each new row added to their entity.

    Basically, I need to know the best way to associate each one of these tables with the one finish table. There is no pre-defined data that the user chooses from, and new data is added to these tables each time something happens.

    As far as the other fields and their data types I am still working on that, I have just started with this database and the finish situation is my biggest issue. I just do not want to over do something to where it is hard to maintain.

    /****** Object: Table [dbo].[Finish] Script Date: 06/22/2012 15:08:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Finish](

    [ID] [int] NOT NULL,

    [Left] [varchar](30) NULL,

    [Right] [varchar](30) NULL,

    [Top] [varchar](30) NULL,

    [Bottom] [varchar](30) NULL,

    [Note] [varchar](150) NULL,

    CONSTRAINT [PK_Finish] PRIMARY KEY CLUSTERED

    (

    [ID] 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

    /****** Object: Table [dbo].[Horizontal] Script Date: 06/22/2012 15:08:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Horizontal](

    [ID] [int] IDENTITY(11,1) NOT NULL,

    [Name] [varchar](15) NOT NULL,

    [Floor] [smallint] NOT NULL,

    [SizeID] [int] NOT NULL,

    [GlassPocket] [decimal](5, 3) NULL,

    [IsFiller] [bit] NOT NULL,

    [Note] [varchar](150) NULL,

    CONSTRAINT [PK_Horizontal] PRIMARY KEY CLUSTERED

    (

    [ID] 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

    /****** Object: Table [dbo].[Door] Script Date: 06/22/2012 15:08:36 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Door](

    [ID] [int] IDENTITY(421,1) NOT NULL,

    [BayID] [int] NOT NULL,

    [Position] [tinyint] NOT NULL,

    [HasJamb] [bit] NOT NULL,

    [HasThreshold] [bit] NOT NULL,

    [IsAutoShowroom] [bit] NOT NULL,

    [IsSingle] [bit] NOT NULL,

    [Type] [varchar](10) NOT NULL,

    [SizeID] [int] NOT NULL,

    [Note] [varchar](150) NULL,

    CONSTRAINT [PK_Door] PRIMARY KEY CLUSTERED

    (

    [ID] 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

    /****** Object: Table [dbo].[Leaf] Script Date: 06/22/2012 15:08:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Leaf](

    [ID] [int] IDENTITY(21,1) NOT NULL,

    [DoorID] [int] NOT NULL,

    [Position] [tinyint] NOT NULL,

    [Stile] [varchar](10) NOT NULL,

    [Bottomrail] [decimal](5, 3) NOT NULL,

    [Hand] [varchar](5) NOT NULL,

    [IsActive] [bit] NOT NULL,

    [Swing] [varchar](5) NOT NULL,

    [SizeID] [int] NOT NULL,

    [Note] [varchar](150) NULL,

    CONSTRAINT [PK_Leaf] PRIMARY KEY CLUSTERED

    (

    [ID] 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

    /****** Object: Table [dbo].[Bay] Script Date: 06/22/2012 15:08:36 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Bay](

    [ID] [int] IDENTITY(1213,1) NOT NULL,

    [ElevationID] [int] NOT NULL,

    [Position] [tinyint] NOT NULL,

    [SizeID] [int] NOT NULL,

    [Note] [varchar](150) NULL,

    CONSTRAINT [PK_Bay] PRIMARY KEY CLUSTERED

    (

    [ID] 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

    Dam again!

  • A couple easy suggestions. Your name of ID is inadequate as Lowell pointed out previously. Your columns don't make any sense to me. There is one thing that will help you immensely as you are designing this. A column name should change its name based on the table it is in. You seem to have ID as the primary key in every table. But when that is referenced as a foreign key in another table the name changes (BayID, DoorID, ElevationID). Those names should be the same in EVERY table.

    I don't understand what you are doing here, so I can't really offer much advice. Well I would strongly advise you to get a book or two on how to structure data.

    I am assuming by your name that you are building rooms/buildings? What is Finish? Is that finish as in Oak, Stainless, etc??? What does elevation have to do with Finish?

    I get that you are just building this but your question is so incredibly vague I don't even know where to start. My fear is that you are WAY over-thinking this. If you can explain what you are trying to do I can try to help guide you a bit.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply