Hello,

I am designing a table to contain property details for an smallish local estate agency. I have various columns as follows:

Property(PropertyID, PropertyLocation, PropertyTypeID, PropertyBedroom, PropertyGarden, PropertyGarage, AskingPrice, BranchID)

I have moved Property Type to a separate lookup table and made another table (Status) which contains the property's sale status and uses PropertyID as a foreign key.

I have a question about the best way to deal with Property Location, This will have repeating values, containing village and town names. I could use a CHECK constraint to avoid mis-spellings, or create another table. I think this may be overkill as town and village names never change, also a town or village would never need a record without there being a property.

I would be interested to know how others would deal with this

Thanks

I wouldn't bother with the location look up table. If I added one it would simply and solely be for use by the applcation to reduce the risk of spelling mistakes. Your UI would need to allow additions to this table on data entry. The check constraint is the worst idea of all - this requires schema changes to allow new locations - eek!

BTW - I presume you record a history of ststuses right? I.e. this is a one to many relationship not a one to one?

Question - what happens if a property comes back onto the market again (probably after several years)?

Thanks for that, I will leave location as a column in the property table But what if a search is needed and spelling mistake have crept in to the locations. The status is a one to many relationship, so properties can be under offer from several different people. Haven't thought about properties coming back onto the market. This is coursework, but I will probably mention that as a possiblity and add some kind of date column.

Outside the brief, but something I was thinking of was a sales table, as that would also be a one to many relationship if the property were sold more than once, as you mention.

Thanks for the help. I am new to this and sometimes it is hard to see how far to normalise and how much is actually a normalisation too far!!

You could use a zip (postal) code lookup table to look up town names. You would probably want to separate the house number from the street name. I suppose you could, if you want to go to the extra effort, include a street-names table as well, but you would need to include an intersection table to handle the many-to-many relationship between property record and street name.

What is often done is to offer the option of some sort of soundex (or similar) algorithm when searching.

Just a thought but if a property came back onto the market after it was sold would it be safe to say that it would get a new PROPERTY ID number. I don't know the buisness rules but I think that make more sense.

Thanks Ioquin, that is a good idea. Another possibility might be to search on postal code only I suppose, with a LIKE query. It is more complex than it appears at first. I think I will leave towns in the property table but look at other options in the documentation.

Tuke, thanks, yes, that sounds like a possibility. The only thing there would be that it might be necessary to look at historical sales. I will again discuss different possibilities in the documentation