...

View Full Version : Split string and use value as foreign key



mike182uk
05-09-2010, 05:59 PM
hi all,

am not sure if this is possible (or a good idea), basically i have a column that contains a string split by commas



1,42,44,12,64,12


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 :-)

thanks alot

mike

Old Pedant
05-09-2010, 06: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.

Example:


Table: foods
foodid : int auto_increment primary key
foodname : varchar(100)

Table: people
personid : int auto_increment primary key
name : varchar(50)
... etc. ...

Table: peopleFoods
personid : int references people(personid) on delete cascade
foodid : int references foods(foodid) on delete cascade

***********

people:
37 : james
42 : mary

foods :
1 : watermelon
2 : dried squid
3 : chocolate covered ants

peopleFoods:
37 : 2
37 : 12
37 : 42
37 : 91
42 : 1
etc.

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.

mike182uk
05-09-2010, 07: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?

I.e

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.

Thanks

Mike

mike182uk
05-09-2010, 08:23 PM
here is my schema:

Attribute table:



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.

thanks

mike

Old Pedant
05-09-2010, 09:34 PM
One more time: Do *NOT* put delimited lists into a database field.

Period.

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.)

mike182uk
05-09-2010, 09: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.

thanks

mike

Old Pedant
05-09-2010, 09:57 PM
You know, you *could* do it this way:



AttributeNames table
aNameID : aName
1 | Colour
2 | Size

AttributeValues table
aValueID : aValue
1 : Red
4 : Large
etc.

ProductAttributes table
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:


ProductAttributes table
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.

Old Pedant
05-09-2010, 10: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.

mike182uk
05-09-2010, 10: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.

thanks

mike

mike182uk
05-09-2010, 11:25 PM
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.

bazz
05-10-2010, 12:57 AM
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.

bazz

mike182uk
05-10-2010, 09:13 AM
hi bazz

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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum