ASE Database forum - Database Design

Author

Database Design

Some User

2005-04-18, 8:24 pm

I got a design question about a table.

I have to store two different items in a table. One is a monetary value.
The other is a percentage.

One person wants to store the monetary value and percentage in the same
column with another column(bit) indicating if it is a money value or a
percentage. Datatype would be decimal 18,2

The other person wants to store the values in two separate columns with
appropriate datatypes.

Is there a common or proper way to handle this or is it just a design
decision/philosophy decision?

Thanks

Pablo Sanchez

2005-04-18, 8:24 pm

Some User <someuser@someplace.com> wrote in
news:pan.2005.04.19.01.03.54.188728@someplace.com:

> One person wants to store the monetary value and percentage in the
> same column with another column(bit) indicating if it is a money
> value or a percentage. Datatype would be decimal 18,2

Pretty basic database design stipulates the domain of the column
should dictate its datatype.

Clearly the person who wishes to overload the column isn't
considering the end of the lifecycle: maintenace.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com

Mark A. Parsons

2005-04-18, 8:24 pm

Ditto what Pablo said plus ...

- if the idea is to save some space ... you won't be saving *THAT MUCH*
space unless you plan on having 100's of millions of rows in this table

- someone coming along after the fact will most likely find no documentation
for this table and may spend a good bit of time pulling out their hair while
trying to remember to code some off-the-wall logic into every one of their
queries that hits this table (ie, the 'maintenance' issue Pablo mentioned)

- the KISS (Keep It Simple Stupid) principle works quite well in RDBMS
design, too

'course, none of the above is applicable if/when you try to figure out the
sysattributes table (or a handful of other Sybase system tables that utilize
bitmap's to store information), or when you take your first pass at figuring
out the contents of sysusers.

Some User wrote:

> I got a design question about a table.
>
> I have to store two different items in a table. One is a monetary value.
> The other is a percentage.
>
> One person wants to store the monetary value and percentage in the same
> column with another column(bit) indicating if it is a money value or a
> percentage. Datatype would be decimal 18,2
>
> The other person wants to store the values in two separate columns with
> appropriate datatypes.
>
> Is there a common or proper way to handle this or is it just a design
> decision/philosophy decision?
>
> Thanks

mpeppler@peppler.org

2005-04-19, 3:24 am

> Some User <someuser@someplace.com> wrote in
> news:pan.2005.04.19.01.03.54.188728@someplace.com:
>
> Datatype would be decimal 18,2
>
> Pretty basic database design stipulates the domain of the
> column should dictate its datatype.
>
> Clearly the person who wishes to overload the column isn't
> considering the end of the lifecycle: maintenace.

Indeed.

Reminds me of a system I have the pleasure of working on,
where somebody who shall remain nameless decided to use a
MONEY column to store all sorts of things, including
dates(!), with a "tag" column indicating what sort of data
is stored.

This table has about 15 million rows, and normal queries
against it involve 4-way self-joins (*shudder*).

I've suggested redesigning that part of the system, but so
far no luck...

Michael

Jeffrey R. Garbus

2005-04-20, 11:24 am

Think about your impression of the system, if you were coming in cold, and
had to figure this out.

Think about the amount of time you're going to have to spend explaining
this, over, and over, and over, and over...

You're not saving anything significant, and suddenly your reporting off of
this number becomes nontrivial.

FYI, beware using float/real for the percentage, use numeric or integer
instead.

(Your associate's suggestion violates 1st normal form, doesn't it?)

Jeff

"Some User" <someuser@someplace.com> wrote in message
news:pan.2005.04.19.01.03.54.188728@someplace.com...
> I got a design question about a table.
>
> I have to store two different items in a table. One is a monetary value.
> The other is a percentage.
>
> One person wants to store the monetary value and percentage in the same
> column with another column(bit) indicating if it is a money value or a
> percentage. Datatype would be decimal 18,2
>
> The other person wants to store the values in two separate columns with
> appropriate datatypes.
>
> Is there a common or proper way to handle this or is it just a design
> decision/philosophy decision?
>
> Thanks

Sponsored Links