should I make my database just 1 table or a series of tables?

[mumzer]

I am building an electronic bulletin board at work. A database is needed to store the information about the items submitted, using MS SQL Server. I feel that I should make the database just 1 table with an itemType field, because most of the data is the same for all item types-vehicles, real estate, home furnishings, events (tickets), and miscellaneous. Only a few unique item fields are needed. My collegue feels that I should make a separate table for each item Type. My 1 table idea would look like this:

Table: tblItem
Fields:
itemID (PK)
userID (PK)
itemType (vehicle, real estate, home furn, events, misc)
itemName
itemDesc
itemPrice
itemYear
city
state
saleRentWant (for sale, for rent, or wanted)
poc (point of contact)
pocHmPhone
pocWkPhone
pocEmail
Approved (y/n)
reType (condo, townhouse, single family, etc.)
vehicleType (car, truck, motorcycle, RV, etc.)
vehicleMake
vehicleModel
vehicleMileage
timeStamp

Where as if I made a separate table for each type of item, I would have redundant fields--all the item___ fields, all poc___ fields, approved, location fields, etc.--in each table.

I would like feedback on which is the right way to build the database. Everyone's feedback is appreciated. Thank you.

--mumzer

 

[petersJazz]

Small comments:

Why do you set userID as primary key. The normalisation rule says that all values is to be dependent of the primary key, and a guess itemType dont depent of userID. And it seams you have a table in the table: looks like poc is an id for pocHmPhone, pocWkPhone, pocEmail so maybee you should make one table for that. It all depends on needs. That is the same with the question if you should have one or many rows.

 

[DaPi]

petersJazz said "It all depends on needs". QUITE!

Splitting off a "poc" table is theoretrically the right answer BUT
The simplicity of the denormalised single table has the advantage of not requiring code for validating against and joining to a "poc" table. The disadvantage is that old data might have old phone numbers (probably NOT a problem for a bulletin board where items remain active for a short length of time) and if ever a whole household of items is sold off a lot of repetitive data entry is required.
"building an electronic bulletin board at work" can you link to an existing "employee" table as "poc"?

Again the purists might argue for different tables for sub-types of "item" - this is a LOT of work and the only advantage I see is that the structure prevents you from allocating vehicleMileage to a micro-wave!
My choice would be to put reType and vehicleType in one field as itemSubType; have itemMake and itemModel instead of the vehicle___ columns.

 

[mumzer]

thanks for the replys. As for making POC and the other POC__ fields a separate table is not needed. They are not required and can be null; it does not need to be validated against because it is only used as contact info on the item listed.

UserID won't be a PK. Only itemID.

All items have an expiration date and are deleted from the database once it expires.