View Full Version : Split string and use value as foreign key
05-09-2010, 06:59 PM
am not sure if this is possible (or a good idea), basically i have a column that contains a string split by commas
these numbers relate to ids of records in another table.
if i delete the record with the id of 42 in the other table, i want to remove it from the string.
with foreign keys you can set the on delete action to cascade (so if 42 would be removed when the record with id 42 is removed)
basically i am wanting to achieve the same thing here, only using the mysql (not php, or any other external language).
can i set a foreign key as part of a substring or something?
the reason i have to do it in a string with commas, is there can potentially be unlimited values, so its not really a good idea to either have 100 columns i'll potentially never use, or create / delete columns on demand.
or is it?
any ideas would be great :-)
05-09-2010, 07:41 PM
Nope. Really bad DB design. You should never store a delimited list of values in a single field.
Time to learn about many-to-many tables.
foodid : int auto_increment primary key
foodname : varchar(100)
personid : int auto_increment primary key
name : varchar(50)
... etc. ...
personid : int references people(personid) on delete cascade
foodid : int references foods(foodid) on delete cascade
37 : james
42 : mary
1 : watermelon
2 : dried squid
3 : chocolate covered ants
37 : 2
37 : 12
37 : 42
37 : 91
42 : 1
This is just *part* of the process called NORMALIZATION. Look it up and be prepared to spend a few hours (okay, at the least many minutes) reading about it.
05-09-2010, 08:07 PM
Hey old pedant, thanks for the reply.
I understand the concept of normalization and many to many table design. The problem is there could be an unlimited number of values. Basically I have an attribute table, an attribute values table, and a attribute combination table. They are linked via foreign keys. But the attribute combination table can't be. Beacuse there could possibly be unlimited number of attributes and attribute values. So how am I supposed to link this up via foreign keys without creating a new column fir each attribute and subsequently deleting it when the attribute is removed?
ID | attribute_combination | price
1 | 1,65,23,12 | 12.99
so I could of have sperate columns for the attributes, but potentially there could be an unlimited amount of values in the combination.
What do you think?
Let me know if I'm not making sense it's quite hard to explain.
05-09-2010, 09:23 PM
here is my schema:
ID | ATTRIBUTE
1 | Colour
2 | Size
attribute values table:
ID | ATTRIBUTE_ID | ATTRIBUTE_VALUE
1 | 1 | Red
2 | 1 | Green
3 | 1 | Blue
4 | 2 | Small
5 | 2 | Medium
6 | 2 | Large
attribute_id foreign key for id in attributes table
attribute combination table:
ID | ATTRIBUTE_COMBINATION | PRICE
1 | 1,2,3 | 2.99
2 | 4,5,6 | 10.99
3 | 6,1,4 | 14.99
4 | 1,2,3,4,5,6 | 99.99
each number delimeted in the attribute_combination refers to the id of a record in the attribute values table. so if they where split up, they would be foreign keys. but there not split up.
so you see my problem? the combination could be 3,6,50,100 values etc.
so would i have to create a new column for each item in the delimeted list? 100 items would mean 100 columns?
surely theres a better way than this? this is why i thought a delimted list was the best way.
any help would be great.
05-09-2010, 10:34 PM
One more time: Do *NOT* put delimited lists into a database field.
Correct contents of your attributeCombination table could be (example):
ID | ATTRIBUTE_VALUE_ID | PRICE
1 | 1 | 2.99
1 | 2 | 2.99
1 | 3 | 2.99
2 | 4 | 10.99
2 | 5 | 10.99
2 | 6 | 10.99
3 | 6 | 14.99
3 | 1 | 14.99
3 | 4 | 14.99
4 | 1 | 99.99
4 | 2 | 99.99
4 | 3 | 99.99
4 | 4 | 99.99
4 | 5 | 99.99
4 | 6 | 99.99
If you insist on using delimited lists in DB fields, then you will be stuck with many more problems than you have encountered so far.
(I suspect that Price does not belong in this table, but that's another question.)
05-09-2010, 10:55 PM
hey thanks again old pedant
the problem is the combination of multiple attribute values affects the price of product.
so if a product is red and small price = £12.99
if a product is gold and xxl price = £15.99
if a products is gold and xxl and has a zip price = £18.99
do you see what im getting at?
red and small = 1,2
gold and xxl = 3,4
gold and xxl and zip = 3,4,5
hence why im delimitering them.
but my problem is the combination could be 2 values or 10 values, totally dynamic so i cant have fixed columns right?
do you see what im trying to solve?
i realize delimtered values in fields is bad design, im looking for an alternative to solve my problem above.
05-09-2010, 10:57 PM
You know, you *could* do it this way:
aNameID : aName
1 | Colour
2 | Size
aValueID : aValue
1 : Red
4 : Large
prodID : aNameID : aValueID
7783 : 1 : 1
7783 : 1 : 2
7783 : 1 : 3
7783 : 2 : 4
7783 : 2 : 5
7791 : 1 : 3
7791 : 2 : 4
7791 : 16 : 136
So each product (or whatever it is you have attributes for) could have as many NAMED attributes, each with as many VALUES, as you needed.
That table, for example, says that product 7783 comes in 3 colours and 2 sizes.
Now, if it only comes in one color in size large (but all 3 colors in size small), and *that* is what you are trying to express, then yes, you probably need to replace "prodid" in that table with "attributeCombinationID" and then have yet another table that associates prodid with attributeCombinationID. In a sense, attributeCombinationIDs become sub-products.
Where something like this gets complicated is when, for example, some attributes are available for all sub-products and some aren't. You don't really want to have to duplicate all the information at the sub-product level for the attributes available to all sub-products, even that's the theoretically correct way to do it. I'm not a fanatic about being theoretically correct if a bit of *careful* denormalization can help keep things simpler or faster, so I would feel too bad about maybe doing:
prodID : subProdID : aNameID : aValueID
7783 : null : 1 : 1
7783 : 1 : 1 : 2
7783 : 2 : 1 : 3
7783 : null : 2 : 4
7783 : null : 2 : 5
Which says that all subproducts of 7783 come in red, but only subproduct 1 comes in green and only subproduct 2 comes in blue. You can probably figure out other variations on this scheme.
05-09-2010, 11:03 PM
LOL. We posted at same time.
FWIW, your problem isn't unique. All big stores of course have the same problem.
I was helping another person on another forum with something similar. And his solution was indeed the concept of subcategories that only applied to some categories.
Not quite as complex as your problem, but the idea of using NULL to mean "all" worked for him.
But I suspect that the really big stores probably do go with full normalization, just because in the long run it's easier to manage with conventional DB tools. And, yes, every single SKU has its own list of attributes. For all the tens of thousands of SKUs.
Gotta go for now. Will check back in later or maybe tomorrow.
05-09-2010, 11:54 PM
hmmmmm, i guess really each variant, should be classed as its own product.
each one is going to have a different price, a different SKU, a different stock level.
although on the flipside they will also share the same product name,product desc, product images, product reviews etc.
so if i was to put them in the same table, i would be repeating portions of data over and over again, which goes againest normalization.
and if a product comes in 7 sizes in 10 different colors, thats 70 entries for 1 product. i sense this will get very messy, and awfully complicated.
what are your thoughts on this?
i will have a look at how other ecommerce solutions try and handle this.
05-10-2010, 12:25 AM
i think the only way of achieveing this with keeping within the rules of normalization is to:
1. have a products table. stores the core product information.
2. have an attributes table. stores available attributes to the products.
3. have an attributes values table. stores the values available for each attribute.
4. have a product variants table, which stores all combinations of attribute values for each product. in this table will be the variant price, variant SKU, variant stock level etc.
to then make this adhere to normalization, the product variants table needs to have a field available for each attribute.
this would mean when adding an attribute, altering the product variants table structure (adding a field) and subsequently removing the field when the attribute is removed. this would also mean removing any variants that depend on this attribute being present (obviously remove these before removing the field).
this seems the only logical way, other than storing delimeted strings. a delimeted string would achieve the same results, although cascading updates etc. would not be achieveable.
which solution would you go for?
man, this is really playing on my mind.
As you have just written, I think that is what Old Pedant suggested.
The table you mention firstly, in your last post - products table - could storee the core product details and the product code. That propduct code could then be your prod_id in the third table OP mentioned.
05-10-2010, 10:13 AM
yeah i was just thinking out loud.
my problem is do i delimit the string, or have seperate columns that are dynamically created / destroyed. or if this a better method. seems a bit overkill. but i aint to sure