Table design question
-
Hi all,
I've encountered a small problem designing a new (and very small) database and was hoping someone could help me out with it.
I baically want to log information about events of some sort. There are 3 types of events, and each has different types of information: Example tables:
Event1 (event1Id, event1data1, event1data2)
Event2 (event2Id, event2data1, event2data2, event2data3)
Event3 (event3Id, event3data1)
Now, I need to also record information that each of these tables shares, such as:
EventAttempt (AttemptId, DateSent, DateReceived, MessageSent, MessageReceived)
An event can have many attempts, but an attempt belongs to exactly one event.
Now I've though about it, and I cannot simply put the eventid as a foreign key into the EventAttempts table because there are 3 event types... so how can I determine which event table the EventAttempts table joins to, Event1, 2 or 3?
One solution would be to have a separate EventAttempt table for each event... but I'm not sure if this is the correct solution.
What is the best way to handle this?
Sorry if this isn't very clear.
Cheers,
Paul
-
paul
i feel its better to have one event table rather than 3 . since there are 3 types of events u can have a "event type" field in the table. in this case u need only one EventAttempt table and u can keep a FKey relationship on EventID field between Event and EventAttempt tables.
This is a more normalised design and in case u have a fourth type of event that needs to be logged in the future u need not create a fourth Event table.
Events (EventID,EventType,event1data1,.....)
EventAttempt (AttemptId,EventID (Fkey),........)
"Keep Trying"
-
Yes, but each event has different information. So if I combine all 3, then there would be NULLs in fields that dont apply to those events. This breaks normalisation rules.
-
yes, Null values should be avoided as they need to be specially handled in data operations. Maybe u cud set some default values for the columns. This would keep the design simple(2 tables) and also handle NULLS.
Another way would be to have child table(s) for the Event table i.e. One primary table for the Event and child table(s) for keeping Event information. This will complicate things a bit specially setting Fkey relationships. If you go for a single child table NULL values would exist in the child table and if u want to do away with as much NULL values as possible then u may want to have more child tables.
Personally i would prefer the earlier solution but its your call. Hope somebody comes up with something better.
"Keep Trying"
-
Another approch is to have two columns in the EventAttempt table, EventID and EventType. Then you could use insert/update/delete triggers to check for ophaned rows.
-
One thing I should mention is that it is HIGHLY unlikely that another event type would ever be added. I simply used the "events" tables as an example. But the real tables have the same problems.
Personally, I don't like the idea of having all these triggers... nor do I like the idea of having NULLs in fields... but I may have to go with one of the options. I'm leaning towards the NULLs...
-
I've just been told to have a single Events table and jam all the different event fields into a single varchar column, with a separator such as a semi-colon...
They can't be serious! 😉
-
Thats not a good idea at all.
"Keep Trying"
-
I second that.
-
Then I second myself.
-
Then I second the other 2 dudes.
So that should make you enough votes to push another idea in the pot and roll over this one
. -
How about something like this:
DECLARE @data TABLE
(
EventIDINT,
DataIdINT,
DataVARCHAR(500)
PRIMARY KEY (EventId,DataID)
)
INSERT INTO @data VALUES (1,1,'Ev1Data1')
INSERT INTO @data VALUES (1,2,'Ev1Data2')
INSERT INTO @data VALUES (2,1,'Ev2Data1')
INSERT INTO @data VALUES (2,2,'Ev2Data2')
INSERT INTO @data VALUES (2,3,'Ev2Data3')
INSERT INTO @data VALUES (3,1,'Ev3Data1')
SELECT * FROM @data
-- You'll need to create views to get the data out for the different events
-- if you are concerned about nulls.
-- Getting the data back out is ugly, but does avoid colums that don't exist
-- for a particular event.
-- Event1 View
SELECT d1.EventId,
d1.Data as FirstData,
d2.Data as SecondData
FROM @data d1 JOIN @data d2 ON d1.EVENTID = d2.EVENTID AND d1.DataId != d2.DataId
WHERE d1.EVENTID = 1
ANDd1.dataid = 1
-- Event2 View
SELECT d1.EventId,
d1.Data as FirstData,
d2.Data as SecondData,
d3.Data as ThirdData
FROM @data d1
JOIN @data d2 ON d1.EVENTID = d2.EVENTID AND d1.DataId != d2.DataId
JOIN@data d3 ON d1.EVENTID = d3.EVENTID AND d1.DataId != d3.DataId and d2.dataid != d3.dataid
WHERE d1.EVENTID = 2
ANDd1.dataid = 1
ANDd2.Dataid = 2
-- Event 3 view
SELECT d1.EventId,
d1.Data as FirstData
FROM @data d1
WHERE d1.EVENTID = 3
-- Someone else could probably provide a more elegant solution to the queries
-- this was my first attempt at them
-- I used a table variable so there is nothing left behind after you run it
-
Thank you for your solution. From first glance, that seems to be one way to go and would avoid NULLs. I'll have a closer look today and see what happens.
Thanks.
-
The problem from this solution is that all fields need to be the same type, i.e. varchar.
I've decided to go with a single Event table with NULLs. Anything else would have its own problems and would add extra complexity which is unnecessary.
Thanks to everyone for looking into this problem.
Cheers,
Paul
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply