Karl,
Typically I would do this as multiple rows rather than comma-separated
data in one field. If you go this route, you may consider adding an
unsigned int primary key (with auto increment) on the discounts table so
you consume less storage space and can do joins more quickly.
`discounts`:
id discount_code product_id_or_whatever
1 sfeijfkjx 34
2 kbgrlijf 36
`user_discounts`:
user_id discount_id
123 1
123 2
456 3
If you really needed to SELECT the data with comma separation, you could
use something like GROUP_CONCAT
(http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat),
but I would try to avoid that.
This is a very basic design principle for relational databases. You may
want to do some more reading about database architecture in general.
That said, you could use a TEXT field to store a comma separated list, I
just wouldn't recommend doing it that way.
-Matt
On 11/26/2012 06:01 PM, Karl DeSaulniers wrote:
On Nov 26, 2012, at 7:29 PM, Bastien wrote:
Bastien Koert
On 2012-11-26, at 7:43 PM, Karl DeSaulniers <karl@xxxxxxxxxxxxxxx>
wrote:
Hello all,
Quick question.
What is the best way to store a comma separated list of discount
codes in a InnoDB?
Text, varchar, blob, ? I want to be able to store discount codes
offered to users in their profile, in their cart, etc.
So I am thinking of storing the codes as a comma separated list that
I can then refer to, add to or remove codes from.
The codes will change every once and a while and they list of codes
may be rather large after say a year,
so I am shying away from an enum() or anything like that.
Any help/pointers is greatly appreciated.
Best,
Karl
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Karl.
Is there a way to group these codes? Can profiles be the same for
different users?
If so another table for the code and a join table for the users to
codes might be simpler to manage
Bastien
Hi Bastien,
Thanks for your reply. Sorry, I should have elaborated.
There is a table that holds all the discount codes. Keeps them grouped
with a product id or if they are to be applied cart wide.
So the field I am asking for will hold codes that are being used by
either a product or by a user or if it is a cart discount.
Each have their own tables, the products, the users and the cart. In
these tables is where I need to insert this field.
An example of the discount table would be like:
Discount table:
| discount code | product id | discount perc.
| discount type |
+---------------------+---------------------+--------------------------+-----------------------------------+
| 8lKzv_=== | 22 | 20
| item |
+---------------------+---------------------+--------------------------+-----------------------------------+
| NpLK23 | 0 |
50 | cart |
+---------------------+---------------------+--------------------------+-----------------------------------+
but in the user table for instance:
User table:
| user_discounts |
+---------------------------------------------------------------+
| 8lKzv_===, YjMlio_===, NpLK23, hDflp0_= |
+---------------------------------------------------------------+
Just trying to figure out the best way to store multiple variables if
you will in one field like so.
Or if this is even the best way to do such a thing. I am open to
alternatives.
I will need to read this new filed out of the database as an array
that I can then utilize with an in_array or something of the sort.
There may be a best practices on this that I am not aware of with my
somewhat limited PHP exp.
Hope that makes more sense.
TIA.
Best,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
