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 12»»

Table Relationships Expand / Collapse
Author
Message
Posted Thursday, August 13, 2009 10:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
Hi I am after some advise on best way of setting out my table structures

Basically I have Person table and the Person can have about dozen different events. Each Event has some common fields but have mainly different data items collected for each event.

would it be best to have a table for each event or would you have an event table which would have all the fields.

I appreciate any advise

cheers
Fred
Post #770663
Posted Friday, August 14, 2009 2:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 1,327, Visits: 4,508
Dot a definitive answer, but a few things to think about which may sway your decision one way or the other.

One of the basic rules of designing your database is "One Table, One Purpose", so how different is each type of event? Are we actually talking about completely different entities for each type of event?

And another rule is "The Key, the Whole Key, and nothing but the Key", so is the primary key (not a surrogate key, but the actual business key) exactly the same for each type of event?

On a more practical point, think about the query you would have to build to list all events for a person. If you have them in separate tables, you will probably have to do a UNION. If they were in the same table, you will possibly end up using CASE statements to differentiate the different types.
How messy would each way be?




Post #770742
Posted Friday, August 14, 2009 3:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
Thanks Ian

i have been given more information to think about now.

seems like they all should not have the same Key to connect them

heeps of thinking to do

cheers

Fred
Post #770764
Posted Friday, August 14, 2009 3:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 1,327, Visits: 4,508
Having just re-read my original post, I don't think I explained the Primary Key bit very well, and I don't want to send you off in the wrong direction because of a bad explanation from me.

The point I intended to make was that if you identify Event type A using different columns from Event type B (e.g. If the primary key for Event type A was the Event Name, but for Event Type B it was Date and Location), they ought to be separate tables. However, if all Event types were identified by their Event Name, and these were unique regardless of the event type, then you could consider using a single table.



Post #770769
Posted Friday, August 14, 2009 7:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 1,920, Visits: 19,348
Hi Fred

try the following code...it will create 4 tables, insert a small amount of data and run a couple of queries.....hope it helps with your thinking.

if you know in advance what types of questions that users will ask of the data... ...it will make the decisions easier in designing yoor structure.

regards Graham


USE [tempdb]
GO
--- create 4 tables in tempdb..all have "x_" prefis
---- for data integrity you could add FKs

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_Person]') AND type in (N'U'))
DROP TABLE x_Person

BEGIN
CREATE TABLE [x_Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[PersonName] [varchar](50) NOT NULL,
[PersonDetails1] [varchar](50) NULL,
[PersonDetails2] [varchar](50) NULL,
[PersonDetails3] [varchar](50) NULL,
CONSTRAINT [PK_x_Person] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)
)
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_EventType]') AND type in (N'U'))
DROP TABLE x_EventType
BEGIN
CREATE TABLE [x_EventType](
[EventTypeID] [int] IDENTITY(1,1) NOT NULL,
[EventTypeDetails] [varchar](50) NULL,
CONSTRAINT [PK_x_EventType] PRIMARY KEY CLUSTERED
(
[EventTypeID] ASC
)
)
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_Event]') AND type in (N'U'))
DROP TABLE x_Event
BEGIN
CREATE TABLE [x_Event](
[EventId] [int] IDENTITY(1,1) NOT NULL,
[EventTypeID] [int] NOT NULL,
[EventName] [varchar](50) NOT NULL,
[EventDetails] [varchar](50) NULL,
CONSTRAINT [PK_x_Event] PRIMARY KEY CLUSTERED
(
[EventId] ASC
)
)
END
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[x_EventPerson_xref]') AND type in (N'U'))
DROP TABLE x_Eventperson_xref

BEGIN
CREATE TABLE [x_EventPerson_xref](
[PersonID] [int] NOT NULL,
[EventID] [int] NOT NULL,
CONSTRAINT [PK_x_EventPerson_xref] PRIMARY KEY CLUSTERED
(
[PersonID] ASC,
[EventID] ASC
)
)
END
GO

--- insert some data

SET NOCOUNT ON
GO
SET IDENTITY_INSERT [dbo].[x_Person] ON
GO
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (1,'Matthew',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (2,'Mark',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (3,'Luke',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (4,'John',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (5,'Paul',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (6,'George',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (7,'John',NULL,NULL,NULL)
INSERT [dbo].[x_Person]([PersonId],[PersonName],[PersonDetails1],[PersonDetails2],[PersonDetails3]) VALUES (8,'Ringo',NULL,NULL,NULL)
SET IDENTITY_INSERT [dbo].[x_Person] OFF
GO

SET IDENTITY_INSERT [dbo].[x_EventType] ON
GO
INSERT [dbo].[x_EventType]([EventTypeID],[EventTypeDetails]) VALUES (1,'Business')
INSERT [dbo].[x_EventType]([EventTypeID],[EventTypeDetails]) VALUES (2,'Social')
SET IDENTITY_INSERT [dbo].[x_EventType] OFF
GO

SET NOCOUNT ON
GO
SET IDENTITY_INSERT [dbo].[x_Event] ON
GO
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (1,1,'SQL Seminar',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (2,1,'AGM',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (3,1,'Monthly Planning',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (4,2,'BBQ',NULL)
INSERT [dbo].[x_Event]([EventId],[EventTypeID],[EventName],[EventDetails]) VALUES (5,2,'Xmas Party',NULL)
SET IDENTITY_INSERT [dbo].[x_Event] OFF
GO

SET NOCOUNT ON
GO
INSERT [dbo].[x_EventPerson_xref] VALUES (1,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (1,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (2,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (3,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (3,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (4,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (5,3)
INSERT [dbo].[x_EventPerson_xref] VALUES (5,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,1)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (6,4)
INSERT [dbo].[x_EventPerson_xref] VALUES (7,2)
INSERT [dbo].[x_EventPerson_xref] VALUES (7,5)
INSERT [dbo].[x_EventPerson_xref] VALUES (8,5)


--- Business schedule query example

SELECT dbo.x_Person.PersonName, dbo.x_EventType.EventTypeDetails, dbo.x_Event.EventName
FROM dbo.x_Event INNER JOIN
dbo.x_EventPerson_xref ON dbo.x_Event.EventId = dbo.x_EventPerson_xref.EventID INNER JOIN
dbo.x_Person ON dbo.x_EventPerson_xref.PersonID = dbo.x_Person.PersonId INNER JOIN
dbo.x_EventType ON dbo.x_Event.EventTypeID = dbo.x_EventType.EventTypeID
WHERE (dbo.x_EventType.EventTypeID = 1)
ORDER BY dbo.x_Person.PersonName, dbo.x_EventType.EventTypeDetails
GO

--- Who's not going to the Xmas Party?

;WITH CTE AS
(SELECT PersonID
FROM dbo.x_EventPerson_xref
WHERE (EventID = 5)
)
SELECT dbo.x_Person.PersonName
FROM dbo.x_Person LEFT OUTER JOIN
CTE ON dbo.x_Person.PersonId = CTE.PersonID
WHERE (CTE.PersonID IS NULL)





______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #770925
Posted Friday, August 14, 2009 8:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
Hi Graham

thanks for this. I will give them a go but just to try to explain to you the scenario.

basically a person has an event which can be different type. some of the events can have records associated to it...

so i was thinking of this

client table (person info) , eventtable (Holds Date and eventid), eventtypes (event type info), associated events records

would i need to create a key to link the eventable to the eventtypes and also the eventtype to the event records.

how do you think i am going? am i all over the place ? :)

Post #771396
Posted Friday, August 14, 2009 11:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 1,920, Visits: 19,348
Fred

suggest you post some sample tables and data (in the same format as my first reply) ...we can then build on that.

I think this will save any possible confusion later on.

you will need keys and probably a cross refernce table to link the tables...but as I said, lets have some data from you.

regards gah


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #771408
Posted Saturday, August 15, 2009 12:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
Your too Kind Graham

can you email me on frecal@internode.on.net

I will email you the info when i get it together

thanks again

Fred
Post #771412
Posted Saturday, August 15, 2009 12:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 1,920, Visits: 19,348
Hey Fred...suggest you post your code here, that's the idea of of having a forum.

plus it allows others, more experienced than I, to also help you.

look forward to seeing your code.

regards gah


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #771416
Posted Saturday, August 15, 2009 1:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
Fair enough

let me see if i can translate to my the business process.

Just one thing...and i think i need to get my head around it...a few of the different events hold quite different information.

regards

Fred
Post #771419
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse