Results 1 to 5 of 5

Thread: Normalisation of this database

  1. #1
    Join Date
    Feb 2003
    Location
    australia
    Posts
    3

    Normalisation of this database

    I want to create a database driven website in Mysql for small businesses that can be accessed by the public. They will search on business type (hairdresser, plumber etc.) and postcode to find a business near their home.

    Each business could have many postcodes that they would service, (i.e. servicepostcode1, servicepostcode2, etc) and many businesses would be listed under several business types: bustype1, bustype2, bustype3 (i.e. plumber, drainer, gasfitter)

    And many businesses will service the whole city

    The flat database could look like:
    BUSINESS (ID, name, address1, address2, postcode, phone, etc, servicepostcode1, servicepostcode2, ... servicepostcode'n', bustype1, bustype2, bustype3, bustype4)

    I don't know how to normalise this database to avoid repeatedly
    entering postcodes and business types.

    Can anybody help a very new member of this forum?

    Thank you all

    Peter

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    Peter,

    Your 3 master tables:

    Business
    ---------------
    BusID (PK)
    Name
    Address1
    Address2
    City
    Phone
    Etc

    PostalCodes
    ------------
    PCode (PK)

    Types
    -----------
    TypeID (PK)
    Type


    Plus 2 association tables:

    Business_Services
    -----------------
    BusID (PK, FK to Business.BusID)
    PCode (PK, FK to PostalCodes.Pcode)

    Business_Types
    ----------------
    BusID (PK, FK to Business.BusID)
    TypeID (PK, FK to Types.TypeID)


    In this way, your postal codes and business types are only entered once in the master tables, and linked to each business through the 2 association tables.


    Jeff

  3. #3
    Join Date
    Feb 2003
    Location
    australia
    Posts
    3
    Jeff,

    Thank you for your rapid reply.

    I'm not clear on the Postalcodes table.
    Should this read:

    Postalcodes
    ------------
    PCodeID (PK)
    Postcodes

    In this case am I not just replacing a 2-4 digit ID for a 4 digit postcode. (there are 1250 postcodes in Sydney)

    If each postcode has it's own ID, and each business can service several (even many) postcodes, then does the table Business-Services become:

    Business_Services
    -----------------
    BusID
    PCodeID
    PCodeID
    PCodeID
    etc.

    Could I not just enter poscodes directly into these columns, and delete the Postalcodes table?

    I'm just a little confused here



    Peter

  4. #4
    Join Date
    Dec 2002
    Posts
    181
    Peter,
    That's one way to go but it's not normalized at all. Having multiple postal codes in the business table is what's known as a 'repeating group' and violates 1nf. Using the postal codes table, each postal code is stored one time and one time only. Here are the nuts and bolts of it:

    create table Business (
    BusID int not null primary key,
    BusName char(10))

    create table PostalCodes
    (PCodeID int not null primary key,
    PCode char(5))

    create table Types
    (TypeID int not null primary key,
    Type char(10))


    Create table Business_Services
    (BusID int not null REFERENCES Business (BusID),
    PCodeID int not null REFERENCES postalcodes (PCodeID),
    CONSTRAINT PK_Buisiness_Services Primary Key (BusID, PCodeID)
    )

    Create table Business_Types
    (BusID int not null REFERENCES Business (BusID),
    TypeID int not null REFERENCES Types (TypeID),
    CONSTRAINT PK_Buisiness_Types Primary Key (BusID, TypeID)
    )

    -- Insert some data

    insert into business
    values
    (1, 'ABC Inc')
    insert into business
    values
    (2, 'XYZ Ltd')


    insert into PostalCodes
    values
    (1, '00001')
    insert into PostalCodes
    values
    (2, '00002')
    insert into PostalCodes
    values
    (3, '00003')
    insert into PostalCodes
    values
    (4, '00004')


    insert into types
    values
    (1, 'BusType1')
    insert into types
    values
    (2, 'BusType2')
    insert into types
    values
    (3, 'BusType3')

    -- Abc inc services 00001 and 00002
    insert into Business_Services
    values
    (1, 1)
    insert into Business_Services
    values
    (1, 2)
    -- Xyz ltd services 00002 and 00003 and 00004
    insert into Business_Services
    values
    (2, 2)
    insert into Business_Services
    values
    (2, 3)
    insert into Business_Services
    values
    (2, 4)


    -- Abc inc is type BusType1 and BusType3
    insert into Business_Types
    values
    (1, 1)
    insert into Business_Types
    values
    (1, 3)

    -- Xyz ltd is type BusType2 and BusType3
    insert into Business_Types
    values
    (2, 2)
    insert into Business_Types
    values
    (2, 3)




    -- show me businesses that are BusType3
    select b.busname from business as b
    inner join business_types as bt on
    b.busid = bt.busid inner join
    types as t on bt.typeID = t.typeID
    and t.type = 'BusType3'

    -- show me businesses that service 00002
    select b.busname from business as b
    inner join business_services as bs on
    b.busid = bs.busid inner join
    postalcodes as p on bs.pcodeID = p.pcodeID
    and p.pcode = '00002'

    -- show me all postal codes that xyz ltd services

    select p.pcode from postalcodes as p
    inner join business_services as bs
    on p.pcodeID = bs.pcodeID
    inner join business as b on bs.busID = b.busID
    and b.BusName = 'XYZ Ltd'


    This way if a postal code or business type ever changes, it need only change in one place.


    Jeff

  5. #5
    Join Date
    Feb 2003
    Location
    australia
    Posts
    3
    Jeff,

    Now I understand! Thank you very much

    Peter

Posting Permissions

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