Hi,

I'm having a problem with my database design. I'm creating a
nightlife listings site and there are a few tables that rely on FK
constraints.

The most important is the 'listings' table, which stores events
that can be viewed from a calendar.

The listing can be of a few types e.g. an event at a night club, a gig,
a special event at a bar etc...
There are tables that hold data about the place that is hosting the
event e.g. there are 'clubs' 'bars' and gigs' tables. So,
each listing needs a parent in one of these tables, and that's where
I'm getting confused.

If I give each parent a FK in the listing table there will be problems
leaving fields blank, and all listings will only have one parent.

I know I could break it up into a separate listings table for each type
but I'd like to be able to add types with out having to make a new
table each time.

Any advice would be much appreciated.

Cheers TWIOF

On 05.09.2006 10:58, TWIOF wrote:
"clubs" and "bars" are locations. So the easiest solution is to put
them into a single table. Then you just have a single FK relationship
for "location".

If clubs and bars are extremely different then you could create an
inheritance hierarchy with location as super class and bar and club as
sub classes (aka related tables). Maybe for the event the info in the
base class / table "location" is enough (address, phone, ...) which
would make querying easier.

I'm not sure about FK's but you can certainly have a constraint that
exactly one of several columns must hold a non NULL value if you want to
have several columns referring to clubs and bars individually. However,
I would not do that but rather choose the "location" approach (above).

Indeed.

Kind regards

robert