[RESOLVED] help with normalizing database

mpar612

Hello,

I am new to databases and I am trying to do things well. Could someone please help me with normalizing this database?

I am creating a database that lists properties. It will list a unique id, address, city, state, zip code, number of bedrooms, asking price, appraised value and a single text box to enter a sentence or two for comments.

In addition, there will be several images for each property. Each image will have a unique id, a path to an image, a title for each image, and the id of the property it is associated with.

Thanks in advance!!!

Here are the two "tables" I have so far:

properties
propId (pk)
address
city
state
zip
bedrooms
askingPrice
valuedAt
comments

image
imageId
path
propId(fk)
title

Sxooter

Good start. As you add things to the master table, think about whether they will be repeated if you add them there. Like you might want to add a listing of features. It would likely be better to have a feature table, and a many to many table in between the main table and the features. So, you could have

features
featureid
featurename

and a table to join the main table to it would look like

proptofeatures
propid (fk to properties, if you can do fks)
featureid (fk to featureids, if you can do fks)

and if say, hottub was featureid 1, and properties 20, 32, and 44 had hot tubs you'd have entries in proptofeatures like:
20, 1
32, 1
44, 1

and so on.

Piranha

Will the askingPrice change, and if so do you want the old prices as well? If the answers is yes to both those questions then you should move askingPrice to another table with a one-to-many relationship.

Another question that might be totally right or totally wrong but not in between. If the company gets the same property to sell again, will it be a new propId or do you want it linked? If you want it linked I suggest that you move everything that may change to one table and keep everything else in this table.

Sxooter have already talked about the comments column, no need for me to get into it as well.

People sometimes talk about the zip and that it is related to the state, city and address. It is true, but it would mess everything up to much to have the zip in a separate table, don't worry about that.

Basically your tables are in 4NF if you don't want to store possible changes in the price and if you start a new row if the same property is being sold again. You have to think about what columns you should have in what table depending on how it will be used.

laserlight

You might also want to read PHPBuilder's own article on Database Normalization And Design Techniques

mpar612

Great. Thank you everyone. The discussion alone has helped me rethink how I am going to do somethings and actually change the functionality of the what I am trying to do. Thanks!