August 13, 2009 at 10:26 pm
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
August 14, 2009 at 2:23 am
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?
August 14, 2009 at 3:26 am
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
August 14, 2009 at 3:51 am
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.
August 14, 2009 at 7:56 am
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
August 14, 2009 at 8:25 pm
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 ? 🙂
August 14, 2009 at 11:38 pm
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
August 15, 2009 at 12:04 am
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
August 15, 2009 at 12:58 am
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
August 15, 2009 at 1:48 am
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
August 15, 2009 at 1:53 am
frecal (8/15/2009)
Fair enoughlet 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
again...sample data will help explain (I hope :-P)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply