What should it be in MySql? In C, it"s an array of integers.

Hi folks --

What would be the right approach in MySql 5.0?

My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach?

I have to manipulate these INTs in my CGI code.

Thanks!

-- Pete




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What should it be in MySql? In C, it"s an array of integers.

Pete Wilson wrote:
> Hi folks --
>
> What would be the right approach in MySql 5.0?
>
> My table, USERS, has columns NAME and IP. Associated with each user is also a collection of from 0 to 50 INTs. What's a reasonable way to put these 50 INTs in the table without using 50 separate columns, INT01...INT50? Is BLOB an OK approach?
>
> I have to manipulate these INTs in my CGI code.
>
> Thanks!
>
> -- Pete
>


Break them out into a separate table linked via the primary key.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What should it be in MySql? In C, it"s an array of integers.

Be careful about burying list type of data in a column.
I've seen poor performance issues parsing lists and XML type data
buried in columns.
A lot depends on your application and how and what you need to query
from those lists.

I've seen a case where a submitted documents were stored in a column
as an XML doc where the application had to fetch all the submissions
within the group and parse them in order to figure out who the
submitters were. This was being done at the gateway page of that tool.
It was a performance nightmare.
An alternative might be to store the integers vertically in a table
with a column describing the position of the value within the list.
Kinda off the cuff but something to think about.

Kyong

On Wed, Sep 16, 2009 at 5:29 PM, John Meyer wrote:
> Pete Wilson wrote:
>>
>> Hi folks --
>>
>> What would be the right approach in MySql 5.0?
>>
>> My table, USERS, has columns NAME and IP. Associated with each user is
>> also a collection of from 0 to 50 INTs. What's a reasonable way to put t=
hese
>> 50 INTs in the table without using 50 separate columns, INT01...INT50? I=
s
>> BLOB an OK approach?
>>
>> I have to manipulate these INTs in my CGI code.
>>
>> Thanks!
>>
>> -- Pete
>>
>
>
> Break them out into a separate table linked via the primary key.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: What should it be in MySql? In C, it"s an array of integers.

> Pete wrote:
> > Hi folks --
> >
> > What would be the right approach in MySql 5.0?
> >
> > My table, USERS, has columns NAME and IP. Associated
> with each user is also a collection of from 0 to 50 INTs.
> What's a reasonable way to put these 50 INTs in the table
> without using 50 separate columns, INT01...INT50? Is BLOB an
> OK approach?
> >
> > I have to manipulate these INTs in my CGI code.
> >
> > Thanks!
> >
> > -- Pete
> >
>
>
> Break them out into a separate table linked via the primary
> key.

How elegant! Thanks.

-- Pete





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What should it be in MySql? In C, it"s an array of integers.

Pete Wilson wrote:
>> Break them out into a separate table linked via the primary
>> key.
>>
>
> How elegant! Thanks.
>
> -- Pete
>
>


it's nothing not taught in Database Design 101. Typically you would
have a setup like this

USERS
USER_ID <--primary key
USER_NAME
USER_IP

ASSOC_NUMBERS
A_ID <--primary key
USER_ID <-- foreign key linked to users
A_NUMBER <---one of the integers that you would store

Alternatively, you can skip the A_ID and have a compound key of USER_ID
and A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though.
Another way I've read about (though I can't remember the article) is
that certain types of databases do allow you to store an array. These
are normally used for databases that need to be very, very small (like
on an embedded chip). However, the way I described is usually the
default for relational databases.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What should it be in MySql? In C, it"s an array of integers.

On Thu, Sep 17, 2009 at 3:46 AM, John Meyer wrote:
> Alternatively, you can skip the A_ID and have a compound key of USER_ID and
> A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though.

Note that this would be marginally faster, because all your data is in
the index, so you don't need to do an additional lookup into the data
segment of your table.

--
That which does not kill you was simply not permitted to do so for the
purposes of the plot.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: What should it be in MySql? In C, it"s an array of integers.

Johan De Meersman wrote:
> On Thu, Sep 17, 2009 at 3:46 AM, John Meyer wrote:
>
>> Alternatively, you can skip the A_ID and have a compound key of USER_ID and
>> A_NUMBER on the ASSOC_NUMBERS table. I prefer the A_ID, though.
>>
>
> Note that this would be marginally faster, because all your data is in
> the index, so you don't need to do an additional lookup into the data
> segment of your table.
>
>

And assuming that the numbers don't duplicate in your array.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org