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

Design question with shared tables Expand / Collapse
Author
Message
Posted Thursday, June 21, 2012 6:39 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
Points: 501, 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!

  Post Attachments 
doorFinish.png (14 views, 16.34 KB)
Post #1319737
Posted Friday, June 22, 2012 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1319950
Posted Friday, June 22, 2012 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 12,905, Visits: 32,167
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1319975
Posted Friday, June 22, 2012 10:37 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
Points: 501, 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... ;)


Dam again!

  Post Attachments 
elevation.png (5 views, 155.32 KB)
Post #1320102
Posted Friday, June 22, 2012 2:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1320167
Posted Friday, June 22, 2012 2:13 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 6:38 PM
Points: 501, 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!
Post #1320170
Posted Friday, June 22, 2012 2:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1320173
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse