Relational Database structure question

[jakeoh]

Hi,

 

I want to design a little classified ads website for fun. Now I am thinking at how my DB should be structured. So far I have this (I omitted certain fields that are not relevant for my question):

 

Ad table

ad_id

ad_title

ad_description

ad_price

ad_startdate

ad_enddate

 

Categories table

cat_id

cat_name

cat_description

 

Ad-Categories table

ad_id

cat_id

 

User table

user_id

user_name

...

 

Ad-User table

ad_id

user_id

 

My questions are:

 

1. Does it seem like a good structure so far? It's my first time designing such a database, so I wouldn't know.

2. If a user posts an ad for the clothes category, he would have to select if the clothes are for men or women, and a specific size (say S, M, L, XL). Now where would that go in the database? In a new table? In the Ad table? In the Categories table?

3. What is the best datatype for the "Price" column? Is it decimal, or should I just use an integer and use php to deal with the decimals (for cents)?

 

Thanks in advance!

 

 

 

[AP81]

1) Yes, that looks nice.  Nice and normalised to 3NF.

 

2) The only real way to adhere to good database design with this is to have separate tables for each category.  For example, clothes would have a table cat_clothes.  Exercise equipment would have cat_exercise. 

I know you probably don't want to go down this path, but there is too much variance between categories and what information needs to be stored.  Storing these in a single table would mean that you are storing a lot of redundant data.  Someone correct me here if you believe otherwise.

 

3) Use decimal(10,2) for fields that will contain monetary values. If you use a float it will round the values, while decimal won't.  I don't see how or why you would want to use an Integer data type here.  You need to store the amount accurately, you shouldn't have to manipulate/calculate prices.

[jakeoh]

1) Thanks!

2) I have no problem doing that (one table per category) as one of my main goals is to practice designing databases... So using that solution, what tables would be required and which relation should be made? I guess I would create a Clothes table, that would relate to the Categories table through cat_id, and then in that table (Clothes) I would cover all possibilities (Women/Men/Unisex - different sizes)? But where is the specific information about, say, that specific Large sweater for Men is kept? In a new Category-Clothes table?

3) Great, thanks.