Suppliers, Services, Postcode Areas and Postcode Districts

[Niall_Deas]

I have hit a wall.... I have a database which matches suppliers with jobs offered. The thing is I cannot for the life of me think of the best data structure to allow the database to automatically select which supplier (c.200 suppliers) who provides a type of service (c.40 types of services) in UK postcode areas (121 UK postcode areas). This is complicated as some suppliers only cover certain postcode districts (c.2250 postcode districts across the UK) (excluding guersey, jersey and IoM). There are an average of 23.36 postcode districts per postcode area (postcode areas = first letters at start, district = numbers before gap in UK postcode).



I have set the table that has a supplier, another with suppliers and service. These link together with a one-to-many relationship. So that part is taken care of. Some suppliers provide many different services so this is required. Question is now how to bring in the postcode areas and districts.

As far as I can see the best way will be to have a one-to-many relationship between a postcode area table and a postcode district table, the postcode district table having postcode area and postcode district fields.

But this is where I lose the plot.

My problem is each supplier can cover numerous postcode areas and districts therein.

Any suggestions appreciated.

[CJ_London]

sounds like you need another table for service Areas with a one to many relationship from your supplier table

tblServiceAreas
ServiceAreaPK autonumber
SupplierFK long - link to your supplier table
PostCode text