January 13, 2015 at 12:59 pm
Here is my basic table design without any relationships added.
USE [MyDatabase]
GO
/****** Object: Table [dbo].[Locations] Script Date: 1/13/2015 2:32:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Locations](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [varchar](50) NOT NULL,
[LocationRoom] [varchar](50) NOT NULL,
[LocationActive] [bit] NOT NULL CONSTRAINT [DF_Locations_LocationActive] DEFAULT ((1)),
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[LocationID] 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
/****** Object: Table [dbo].[Sessions] Script Date: 1/13/2015 2:33:38 PM ******/
CREATE TABLE [dbo].[Sessions](
[SessionID] [int] IDENTITY(1,1) NOT NULL,
[SessionName] [varchar](50) NOT NULL,
[SessionDate] [varchar](50) NOT NULL,
[SessionTime] [varchar](50) NOT NULL,
[SessionCapacity] [int] NOT NULL CONSTRAINT [DF_Sessions_SessionCapacity] DEFAULT ((25)),
[NumberRegistered] [int] NOT NULL CONSTRAINT [DF_Sessions_NumberRegistered] DEFAULT ((0)),
[SessionOpen] [bit] NOT NULL CONSTRAINT [DF_Sessions_SessionOpen] DEFAULT ((1)),
CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED
(
[SessionID] 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
/****** Object: Table [dbo].[Registrants] Script Date: 1/13/2015 2:35:31 PM ******/
CREATE TABLE [dbo].[Registrants](
[RegistrantID] [int] IDENTITY(1,1) NOT NULL,
[RegistrantFirstName] [varchar](50) NOT NULL,
[RegistrantLastName] [varchar](50) NOT NULL,
[RegistrantDOB] [varchar](50) NOT NULL,
[RegistrantEmail] [varchar](50) NOT NULL,
CONSTRAINT [PK_Registrants] PRIMARY KEY CLUSTERED
(
[RegistrantID] 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
There is a one-to-many relationship between the Locations table and the Sessions table. There is a one-to-one relationship between the Sessions table and the Registrants table. I need to be able to populate a cascading dropdownlist with the appropriate sessions for each location when the selected location changes (without throwing a unique constraint exception in asp.net). I also need to associate each registrant with one and only one session when I insert a new registrant.
Should I use a bridge table between the Locations and Sessions tables and use the bridge table ID in the registrants table? Or should I simply just use a foreign key in the Sessions table from the Locations table and make them a compound primary key to uniquely identify a session?
What do you think?
January 13, 2015 at 1:31 pm
Technically you should still be dealing with a logical not a physical design, i.e. "entities" rather than "tables". but let's jump ahead to tables anyway, I guess.
You're missing at least one table, since Sessions seems to be both a global control for Session -- Capacity, RegistrantCount, etc. -- and a detailed list of individual participants in the Session. Those two should be separate tables, a "Sessions" and a "Sessions_Details" or whatever you want to call it to associate a location and registrant to that specific session.
If:
a Session must have 1 and only 1 location associated with it, then you should add LocationID to a detail session table, and use a natural key of:
(LocationID, SessionID)
on the detail Sessions tables.
Likewise, if a Session must have 1 and only 1 Registrant associated with it, you can add Registrant to the detail Sessions table as a FK back to Registrants.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 13, 2015 at 1:45 pm
I have a working version of this that is limping along by using a UniqueSession table as a bridge between Locations and Sessions. My boss is unhappy that I used the bridge table and says I should have just put the LocationID in the Sessions table as you suggested.
Right now I am putting the UniqueSession ID in the Registrants table to associate each registrant with a unique session/location.
It works, but I fear it is not the best design.
I don't understand your comments about talking logical design instead of tables. Can you elaborate?
Thanks
January 13, 2015 at 1:56 pm
RHJ (1/13/2015)
I have a working version of this that is limping along by using a UniqueSession table as a bridge between Locations and Sessions. My boss is unhappy that I used the bridge table and says I should have just put the LocationID in the Sessions table as you suggested.Right now I am putting the UniqueSession ID in the Registrants table to associate each registrant with a unique session/location.
It works, but I fear it is not the best design.
I don't understand your comments about talking logical design instead of tables. Can you elaborate?
Thanks
You're right overall. There must be at least one other, separate table that relates the other tables together. You can call it "UniqueSessions" if you like.
That table would contain only:
LocationID
SessionID
RegistrantID
and any other intersection data about the relationships among those. It would not have any data about just location or just session, only data about the entire session relationship.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply