Re: CSV storage InnoDB?

On Nov 26, 2012, at 9:22 PM, tamouse mailing lists wrote:

> On Mon, Nov 26, 2012 at 8:10 PM, Karl DeSaulniers <[email protected] 
> > wrote:
>>
>> On Nov 26, 2012, at 8: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  
>>>> <[email protected]> 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
>>>
>>
>>
>> I think I may have asked this already a year or so ago, but cannot  
>> find my
>> old emails.
>> Sorry for any redundancy.
>>
>>
>> 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
>>
>
> Hi, Karl,
>
> Your idea can work -- storing the list of discount codes as comma
> delimited (or some known delimiter that won't show up in the codes) is
> pretty easy, and when you fetch it out you can explode it into an
> array, and use in_array to check. (Something you might want to
> consider is storing it as a JSON string as well.)
>
> There's also the "right" way to do this, for some value of "right",
> which is the many-to-many relationship using a foreign key table. This
> can be painful if you're not really strong on SQL, especially dealing
> with updates and deletes. (Example: if you delete a discount code, you
> need to delete the associated records in the foreign key table.)
> Generally this sort of operation is best done as stored procedures,
> rather than multiple queries from PHP.
>
> Question: do the discount codes only apply to one product, or can a
> discount code be used for several products? If the latter, then you
> have the same sort of issue of a many-to-many relationship, requiring
> the foreign key table there...
>
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


Hi guys,
Thanks for your responses.
I do have a auto incremented id for the codes. Yes multiple product  
can have the same code. Table is as follows.

CREATE TABLE `discounts` (
   `d_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `p_id` int(10) unsigned NOT NULL DEFAULT '0',
   `d_discode` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `d_type` enum('item','cart') NOT NULL DEFAULT 'item',
   `d_discamt` float(5,2) unsigned NOT NULL DEFAULT '0.00',
   `d_discper` int(3) unsigned NOT NULL DEFAULT '0',
   `d_qty` int(10) unsigned NOT NULL DEFAULT '0',
   `d_description` varchar(25) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `d_expire` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `d_user` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `d_userip` varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `d_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   PRIMARY KEY (`d_id`),
   KEY `d_discode` (`d_discode`),
   KEY `d_expire` (`d_expire`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

This is where the discounts and their codes and any info are stored  
including who set the discount (this user does not apply to our  
situation, FYI).
Then in say the user table I set the codes that user gets to use.
...
  `u_discodes`  (???This is my question. What to use???) CHARACTER SET  
utf8 NOT NULL DEFAULT '',
...

I just need to store the separate codes a user has available to them  
here. So what would be the best suited?
I am thinking a blob or text, because like tamouse said, I can explode  
the results and or check an in_array on the results.
It seems the logical route to me for what I am doing. u_discodes just  
is a reference to check against.
I then apply the amounts or percentages according to what is in the  
discounts table for that code.

Am I moling this?  Missing something?
I'd like ( I guess ) to stay away from creating separate tables that  
hold the references for users, products, carts, etc and their codes.
To me the blob seems so much simpler.

But alas, I may have answered my own question.

: \

Best,
Karl
Karl DeSaulniers
Design Drumm
http://designdrumm.com


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php