TalkPHP - View Single Post

buggabill · 01-29-2008, 02:02 PM

Quote:

As buggabill said, if I need to add/remove a property in my design I'd have to add/remove a column to the table and change all my querys, but in your design if I have 30 items and I wanna add/remove a property I also have to insert/delete 30 rows.

Why?

Just delete the row that you need to delete, or add a new row to the table. The item_id column is the important one.

For example, if you wished to get all of the properties for one item using my example:

Code:

SELECT 
   	item_property, property_value 
FROM 
	tbl_properties
WHERE
	item_id=1244

It does not matter what order the properties are in the table, just that they all share a common item_id. Just make sure to index the table - at a minimum - on the item_id. Add the properties to the table making sure that the item_id is specified correctly. Also, removing a property from all items would be a heck of a lot easier.

Code:

DELETE FROM 
	tbl_properties 
WHERE 
	item_property="MegaPower HP"

You do not have to have multiple tables for the properties for different item types. That was just something that was mentioned earlier. You're approach of hard-coding the tables with property names will make the database monolithic at best and more like a total pain in the butt to maintain in the future.