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

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

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

:confused:

Peter

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

Jeff,

Now I understand! Thank you very much

Peter