SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design question with shared tables


Design question with shared tables

Author
Message
BuilderBits
BuilderBits
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1365 Visits: 251
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!
Attachments
doorFinish.png (19 views, 16.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63077 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72488 Visits: 40942
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!
BuilderBits
BuilderBits
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1365 Visits: 251
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... Wink

Dam again!
Attachments
elevation.png (11 views, 155.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63077 Visits: 17959
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. :-P

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
BuilderBits
BuilderBits
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1365 Visits: 251
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63077 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search