sql theory - sub tables

[gwu]

I need to store an event in our application. The event can be specific to a user or it can be created by the app administrator for all users to see. What is the best way to store this info while still keeping the foreign key restraints intact. Here is the begining of the design:

Person table
personid name
1        frank
2        steve

event table
eventid description personid
1        lunch      2

Possibilities I have come up with:

1)foreign key that allows nulls(I don't like this idea)

2)Could this be done in a way there are three tables?An event table which store all events and two sub event tables one for admin events and one for personal events. How would this work?

thanks

 

[JamesLean]

Personally I would use option 1. There is nothing wrong with a FK which allows NULLS.

--James

 

[micha123]

What kind of queries will you issue by that application?
This is very important in order to design the correct tables!

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[vongrunt]

Without extra information, personally I'd go with option #1. Saves time

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

Just remember that you can't join 2 tables on nulls as nulls never = each other..

So rows that come back will never have a Null FK.

(at least by default and without running some set options or using isnull - which gets expensive on tables wiht many rows...)


My 1c


Rob