Member Avatar for Cool&Awesome

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 because: n/a
  • 2 Contributors
  • 4 Replies
  • 132 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Cool&Awesome
Member Avatar for aquilax 
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 because: n/a
Member Avatar for Cool&Awesome

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.

Member Avatar for aquilax

Please tell me where do you find difficulties. I'll try to clarify.

Member Avatar for Cool&Awesome 
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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.