0

Hi everyone,

I have a database with X items, and each of them can have one or more of 20+ properties.
How I currently make this work is I have a huge table with each of the properties having it's own column, and a boolean to determine does the property apply to an item. Something like this:

---------------------------------------------------------------------
ITEM PROPERTY 1 | PROPERTY 2 | PROPERTY 3 | etc.

some item 0 1 0
another one 1 1 0
one more 1 0 1
---------------------------------------------------------------------

Can having properties in a separate table and using many to many relationship
make this any more practical? And how to do it? Or any other solution?

If anyone knows this please give step by step info for the sake of all of us
database noobs who get stuck at this turning point where you realize database design
is WAY more than making a table and choosing column types.

Thanks:)

Edited by Cool&Awesome: n/a

2
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by Cool&Awesome
0
table properties
  id
  name

table vals
  id
  item_id
  property_id
  value

To get all poperties for item:

Then use this to get all properties for particular item:

SELECT 
* 
FROM vals v
RIGHT JOIN properties p ON v.property_id = p.id
WHERE item_id = 123
ORDER BY p.id

Edited by aquilax: n/a

0

Thanks, this got my thinking moving in the right direction, but still unable to implement it myself.

Roamed around the net for solution but all the explanations seem too general. Guess it would be too much to ask for "line for line" explanation, but if there's any book/tutorial that really gets into details, I'll appreciate the recommendation.

0
table properties
  id
  name

table vals
  id
  item_id
  property_id
  value

To get all poperties for item:

Then use this to get all properties for particular item:

SELECT 
* 
FROM vals v
RIGHT JOIN properties p ON v.property_id = p.id
WHERE item_id = 123
ORDER BY p.id

Thanks for your reply - I solved it thanks to this, it works now. What I have found out in the process, is that you don't have to have id column in vals table, as item_id and property_id can together form a primary key.

I'm totally unclear on what RIGHT JOIN means despite some reading, but I found the way to make my queries work, I think I'm doing JOINs implicitly.

--

btw.in one database I've set a foreign key, one-to-many (product-reviews), so products have id's (1,2,3...) ...now when I try to insert a review via phpMyAdmin (for testing), for each product (say it's id is 15), it offers me 2 ways of input:
-15
and
15-

Can you explain me what this represents, which one to choose?

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.