hello. i was wondering what the best approach was: having a table
where each row has a field that holds more items (say 10), so that
each row gives me all the items associated with a certain request, or
a table that returns more rows with exactly one item per row
associated with that request?

an example: a given user may have a list of cities associated with
him; is it better to have a table where each username has exactly one
column that lists all the cities, or exactly one row for each city
associated with that user?

i see the second option's obvious advantages for possible
calculations, but would like to know more about the performance. is
there a general rule for situations like this?

thanks.

omeldoid@gmail.com wrote:
> hello. i was wondering what the best approach was: having a table
> where each row has a field that holds more items (say 10), so that
> each row gives me all the items associated with a certain request, or
> a table that returns more rows with exactly one item per row
> associated with that request?

The advantage to have a column with X items is that you only need to fetch one
row, but the disadvantage is that you loose the relations between the items
and extra information and searches will get more complicated.


> an example: a given user may have a list of cities associated with
> him; is it better to have a table where each username has exactly one
> column that lists all the cities, or exactly one row for each city
> associated with that user?

I highly recommend you have one row for each link user to one city.
If the table you are thinking of has more info than just username and city,
then you create a link table which links the user to a city. As you should
avoid to duplicate data.


> i see the second option's obvious advantages for possible
> calculations, but would like to know more about the performance. is
> there a general rule for situations like this?

Having all in one column, would ease the databases load when using simple
SELECT, but you tenfold the load on your script, which has to do all the stuff
that the database had done fast with low CPU usage if you had a row for each
link user-city.
It's a higher risk that you make a bad code in your script than the code that
makes up the database server, so you would have the risk that you even let the
load on your script to be even higher.

The better design you have on your database, the better things will be in the
end, making shortcuts will just make things worse.

--

//Aho

On Nov 3, 1:15 pm, "J.O. Aho" <u...@example.net> wrote:

> I highly recommend you have one row for each link user to one city.
> If the table you are thinking of has more info than just username and city,
> then you create a link table which links the user to a city. As you should
> avoid to duplicate data.

> Having all in one column, would ease the databases load when using simple
> SELECT, but you tenfold the load on your script, which has to do all the stuff
> that the database had done fast with low CPU usage if you had a row for each
> link user-city.
> It's a higher risk that you make a bad code in your script than the code that
> makes up the database server, so you would have the risk that you even let the
> load on your script to be even higher.

a highly informative answer. thank you very much.