Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Relationships


Table Relationships

Author
Message
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
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
Ian Scarlett
Ian Scarlett
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: 1420 Visits: 6250
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?



frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
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
Ian Scarlett
Ian Scarlett
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: 1420 Visits: 6250
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.



J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3443 Visits: 32970
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

frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
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 ? Smile
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3443 Visits: 32970
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

frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3443 Visits: 32970
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

frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
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
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