Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Simple normalizing question

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Smile

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

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

  4. #4
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    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?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    I've been really happy with Happy Fish.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •