PHP/MYSQL DB Question

Some assistance required for DB structure

[woldie]

Hi all,

I've got this case study, and just need some direction on this one, I've made a start on the DB structure but anyone who thinks I'm on the wrong track then any assistance would be much appreciated.

Basically I need to provide a online calculator on the information provided below:

Detached Semi-Det Terraced
AL1 £574,315 £327,751 £252,402
AL10 £331,028 £227,398 £181,546
AL2 £414,169 £264,200 £229,891

What you have is ZIP Code/Postal Code on the left, the type of property across the top, and then the values for each type of property.

What I want to is get the user to select a postcode from a drop down box, select a type of property, which then gives you the value of the property.

Example: if you select ZIP CODE AL10, and then select Semi-Det you should get the value £227,398.

This is what I have done DB structure wise is as follows:

Table name: postcode
pid
pcode

Table name: House Type
tid
house_type

Table name: house_price
prid
tid
h_price

Table name: house_map
hmid
tid
pid

This is what the DB structure is at the moment, now I'm not quite sure if I am on the right track.

Any Ideas?

Cheers

[Netizen]

Surely you only need three tables: postcodes, house types and prices.

Each real postcode is assigned an id in the postcodes table, each house type is assigned an id in the house types table, and then prices are listed against postcode id and house type id in a prices table. In the prices table the postcode id and house type id are the primary key as there is only one price for each unique pair.

Personally, I tend to go for obviously named columns in my tables as well i.e. postcodeID, houseTypeID, etc.

[woldie]

Thanks Netizen,

Something like this:

Table name: postcode
pid
pcode

Table name: housetype
tid
house_type

Table name: price
prid
pid
tid
price

Do I need to setup any unique IDs?

Thanks

W

[Netizen]

I would say you don't need prid field in the price table, and a unique key of pid, tid