Joining tables over a set field

gbs3769

I'll try and describe this question as best that I can, if you need more information please let me know and I'll try and give it to you.

I have a database that has a couple of tables set up. The first table looks something like this.

| item_id | item_name | item_properties |

item_properties is a set from 1 to 52

I have a second table that looks something like:

| property_id | property_name |

property_id is a value from 1 to 52 and each one is unique

Basically what I want to do is select an item from the first table, which may have more than one property but have the property names show up instead of the value that is in item_properties.

So basically the result would look like

| item_id | item_name | property_name 1, ... , property_name n|

I know that I can do it a couple of different ways. I have used str_replace but that is a pain, especially as I go to add more properties to the second table and I have also used a second query. I was hoping that there might be a way to get it all done in one simple query.

Like I said, if this is confusing please let me know and I will do my best to explain it in a little more detail. Thanks for the help.

Matt

LordShryku

When you say it might have more than one property, how are you storing it in this case? Is it all in one field, somehow delimited? Or do you do it as seperate entries?

gbs3769

Originally posted by LordShryku
When you say it might have more than one property, how are you storing it in this case? Is it all in one field, somehow delimited? Or do you do it as seperate entries?

It is a set field, so it is delimited by commas.

Matt (2)

LordShryku

Well, that's bad database design. You're not going to be able to explode on commas in the SQL, so you're not going to be able to do this with a join. You're going to have to do it in PHP.

gbs3769

What would be a better design idea? I guess I'm a little too new to understand the best ways to design databases.

Matt

LordShryku

The best thing would be to add a seperate row for each property. That way, you would be able to join the tables and get this done in one query. The way it's set now, you'll have to query on first table, explode the item_properties on the comma, and perform another query to get the values from the second table.

gbs3769

I just wanted to revisit this.

To me, it doesn't seem to make any sense to have a different field for evey property. Every item that is in the database is not going to have every property. In fact, most items will only have one or two properties.

This is where I thought that using a set structure would be more beneficial. What about changing the set structure over from using numbers, to using the actual properties. Each property is only a VARCHAR with a maximum size of about 20. This would pretty much allow me to do what I want to do. Is there an easy way then to convert the properties over from using numbers to using the actual name of the property?

I tried to do this but it kept erasing properties if there were multiple ones. I guess this wouldn't be such a large problem since there are only about 80 items in the database that would then have to be edited because they had multiple properties.

Your thoughts?

Matt

LordShryku

My thoughts: regardless of what you use, if you decide to join multiple properties in one field and delimit them, you won't be able to do this with one query. The way I explained it is the point of having a Relational Database. Anyway, it's up to you. That's just my two cents.

gbs3769

Originally posted by LordShryku
My thoughts: regardless of what you use, if you decide to join multiple properties in one field and delimit them, you won't be able to do this with one query. The way I explained it is the point of having a Relational Database. Anyway, it's up to you. That's just my two cents.

Then what is the point of the set field type? It seems like for my purposes that the set field type would be beneficial, essentially it would have elements from say {"a", "b", "c"}. Some items would have elements {"a"} others would have {"a", "c"} and all other different combinations. It just seems like it would make the table way too large if each "property" was a different field. I would have at least an extra 52 fields in my table, with the actual values in each field being some boolean identifier telling me whether or not the item holds property "a" or "b" or "c" and so on. Am I explaining this wrong, or is that really how you would expect to design the database?

Matt (2)