Hi

My first post so bare with me! I'm trying to normalize a table and need some advice on how to set up one particular relationship.

Basically, i have a table of different venues (eg pubs, resturants etc) and each venue has a varying amount of different facilities. At the moment i have a seperate table which has an auto incremented integer for a primary key against each facility name (eg, 1 - toilets).

Back in the venue table i am listing the facilites against each venue in a single comma delimited field.
eg
pub A - 1,3,4
pub B - 3,5

I know that 1st NF requires that you get rid of this but i'm getting stuck as to what will work best.

I've come up with the following:

Keep the facilities table, (eg 1 - toilets) but add a second table where the venu id (fk) lists each faclitiy seperatly, eg (from example above)

venu_id (fk) - facility
pub A - 1
pub A - 3
pub A - 4
pub B - 3
pub B - 5

Only now i'm repeating the venue_id and feel this may be a waste of space, is there a better way?

many thanks in advance

no, that is the best way, one row per venue facility -- the venue id is a foreign key, the facility id is a foreign key, and the pair of them together is the primary key

by the way, the expression is "bear with me"

"bare with me" means we both get undressed together, and i'm not gonna

lol, how about a 'beer' with me then?

Thanks for your answer, great forum, given me a bit more confidence with normalization!

ps, don't suppose you know of any freebie graphics programs which will do the layout, i've found one - 'Fabforce database deigner', how ever i can't see anywhere where i can add the 'crows feet' or 'one' or 'zero' icons?

see http://www.databaseanswers.org/modelling_tools.htm

i've tried DBDesigner and i don't like it, it messes up PKs and/or forces a particular naming scheme on you

I've been really happy with Happy Fish.