Re: CSV storage InnoDB? - Solved

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



You have a valid point there Matt.
Not dead set. Table it is.

Thanks guys.

Best,
Karl


On Nov 27, 2012, at 12:30 AM, Matt Pelmear wrote:

BLOB and TEXT are basically the same thing, except that BLOB can be used for storing binary data (like an image).

You would only need one additional table to do what I was describing, but if you're dead-set on using a comma separated list then TEXT or LONGTEXT is probably what you want, depending on how large you anticipate those lists becoming.
If you care about efficiency you may want to look at this blog post: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/

-Matt

On 11/26/2012 09:55 PM, Karl DeSaulniers wrote:

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


Karl DeSaulniers
Design Drumm
http://designdrumm.com


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



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux