Posted by Jerry Stuckle on 09/21/05 15:02

elyob wrote:
>
>
> Not disk space, but creating multiple rows will require more work from the
> server. Personally I like the idea of keeping similar stuff in a single row
> in the same master table rather than create multiple rows in a seperate
> table. I'm looking at this as a technique for other stuff too, e.g. hotel
> facilities.
>

And keeping everything in one row will require more work from the
server. Every request for a row will have to get more data, for
instance. This will require more disk space, more memory and
potentially more processing time. Additionally - it becomes much more
complex to add a new item.

> One of my data suppliers for hotels has over 100 columns in one of their
> feeds supplting details such as "internet 0", "minibar 1", "satellite tv 1"
> ... personally one column aggregating all this stuff would be ideal. Maybe
> overly complex at first, but saves a lot of extra work when populating and
> reading that table.

Hmmm, sounds like a perfect example for a linked table. Aggregating the
data IS more complex - both at first and later. And again - what
happens if they add a new feature - like a sauna - to a room?

> So, rather than expand my table with 6 extra columns, I just add one column
> with called "payment_type".
>
>
>>(3) Minimum disk I/O used in updating the database
>>(5) Minimum disk I/O used in querying the database
>>(7) Minimum number of database rows
>>(8) Minimum number of database columns
>>(9) Minimum coding time for MySQL queries
>>(13) Minimum (3) plus (5) (You have to state an assumed ratio of
>> updates to queries).
>

Maximum processing time encoding/decoding the column, maximum code
changes when adding a new payment type...

>
>
> It's less about optimisation and more about tidy, concise tables. I just
> don't feel right about creating either 100 columns for every snippet .. e.g.
> for restaurants "kids menu", "vegetarian menu", "vegan menu", blah blah blah
> ...
>

And properly a properly normalized database is tidy and concise. Don't
create 100 columns for each restaurant. Create a table for restaurants,
a second one for option types. Then the third one is a simple link
between the restaurant and available options. Clear, clean and concise.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]