Query about database setup - am I doing it wrong? :o)

[pepsi_max2k]

Hey all, I'm about to start coding an online store. I have one database full of various items (with basic info ie. title, format, and prices linked to them) and think it's best if I create another table for the main store, and join in item entries from the main item table when needed. The thing is, not all items will be present in the first table, and I won't always want the price included as it is in the first table (actually, it's in a second table, but it's complicated enough...). I also want to add in extra info (like condition, random comments, etc). So how does the following look?

 

=== Table 2 ====

Item ID (link to T1 item ID for details and price)

Item Name override (if there's no item ID in T1, ie. Item ID is blank)

Price Override (if I want to override the price from T1)

Condition 1 (case / box)

Condition 2 (sleeve / inserts)

Condition 3 (manual)

Condition 4 (disc / item)

Other Condition (anything that doesn't fit the above)

Comments (for random comentary for anything that needs a better description than just item title and condition)

Image (maybe, possibly, I seem to recall adding images to a database is horrendously hard, maybe just a link to one then, though keeping track of images that need to be present and those that dont (for sold out items) should be fun...)

 

I think that looks just about ok. Looks like I could possibly split out condition in to a seperate table, though doesn't really seem like enough to warrant it (it'll be literally a choice between 0 and 5 for each condition, relating specifically to the one product entry). I should probably google how to code a store... or just stick to eBay... 

[trq]

As soon as you start to creating fields named the same (eg; condition1, condition2 etc etc) you are off the tracks of good design. Idealy these conditions should be within there own table so that you are not limited to the amounts of fields required. You already have an extra field (Other Condition) there just in case you need it. This is a poor design choice.

[pepsi_max2k]

Yeah, I thought as much, but... a table purely for "Condtion"? What does it contain? Product ID plus four condition columns, each with an individual rating, each only even relating to the individual product id? Seems to me I wouldn't be cutting down on any replication, just moving out data that relates to a specific product out of one table's entry for that product and in to a seperate table.

 

Edit: Only reason for doing it I can think of - if I had a new table for "Contents", then could add a list of contents and rate their condition, linking them to the product id. If I just assume everything has much the same contents, having five extra columns for each product with a rating between 0 to 5 (or N/A) doesn't seem so bad.

[trq]

But moving them out into there own table you do away with the need for 5 fields. You just add a new record for each condition, whether that be 1 or 100.

[pepsi_max2k]

Hmm... not really sure I understand, I'll read up on stuff when it's not past midnight For now, here's a pic of what I meant above. Will eventually be displayed on a page like this:

 

Item: 'name' - linked from 'ID' or directly from table below

Price: linked from 'ID' or 'Price'

Stock: 'stock'

Condition

Box: 'conditionA'

Sleeve: 'conditionB'

Manual: 'conditionC'

Disc: 'conditionD'

 

(Or General Contents: 'conditionE' displayed if available and the above are missing)

 

'comment'

 

[attachment deleted by admin]