[thelist] is there something like eval() in my sql?

Ken Schaefer ken at adOpenStatic.com
Tue May 25 08:59:15 CDT 2004


Hi,

That's definately not a normalised design. When you denormalise things (eg
for performance reasons), then you start having problems using SQL to
generate any sort of summary data because SQL doesn't work well with
denormalised data (which is why we have a normalisation process - to allow
us to query our data using SQL!)

Whether you need a separate table or not (for periods) depends on what your
entities are. If you do a decent ER (Entity-Relationship) diagram, then you
will (99% of the time) end up with a good DB schema. The question you need
to ask yourself is "is the period an entity in itself. Does it have
attributes?" If not, then I would hesitate to create a separate table for
it, unless there are good performance reasons for doing so. On the other
hand, if StartDate and EndDate are merely attributes of an individual price,
then they should be stored in the Price table (Prices are your entity, and
the StartDate and EndDate are merely attributes of that entity).

If you have a properly normalised DB schema (with tables for all the
entities, and fields for all the attributes), then all your SQL will work
just wonderfully. Whether the DB server can cope with the size of the tables
is another issue :-) But, up until the DB server can't cope, I would stick
to a normalised design. Once the server is starting to become overloaded,
and assumign you can't tune performance anymore, you may wish to consider
either (a) denormalisation or (b) moving older, less used data to an
archival server or (c) having a separate server for OLAP queries which may
be unduly stressing the server when running queries designed to summarise
large amounts of data.

Cheers
Ken

----- Original Message ----- 
From: "Richard Bennett" <richard.bennett at skynet.be>
Subject: Re: [thelist] is there something like eval() in my sql?


: On Tuesday 25 May 2004 14:42, Ken Schaefer wrote:
: > Sounds like sub-optimal table design to me.
: You could be right there...
:
: > If you normalize the data, then
: > you'd just be able to put a criteria in your WHERE clause. Is
normailizing
: > the data an option?
: Ok, the real story is:
:
: I have to match phonecall-records to a pricelist.
: The pricelists have a monthly valid period, roughly one list per month,
and a
: prefix column to link to the call-records by.
:
: currently the pricelists don't have a fromDate or toDate column, there are
: simply seperate tables for each period.
:
: So I was trying to link to the correct pricetable depending on the date of
the
: call - which doesn't seem to work for many reasons.
: And thinking about it now, it looks like i will have to put all the
pricelists
: into one big table anyhow...
: I was hoping to avoid this, as each list is 80.000 records as it is...
:
: Will it be best to put all the pricelists into one big table, with
: datefrom/dateto columns for each record, or to use a third table with the
: datefrom/dateto columns, and link by tableID?
:
: Richard



More information about the thelist mailing list