Re: Is multi-type data in one field normal?

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:XoCla.11$037.222_at_news.oracle.com...
> "Mike" <Star.Point_at_mcsci.net> wrote in message
> news:a90c0da6.0304110155.1aacad7d_at_posting.google.com...
> > tblLabor (Example)
> > Labor Labor Statistic
> > UserID CaseID Date Hours Amount StatisticTypeID
>
> --------------------------------------------------------------------------
> -
> > Emp1_ID ABC_ID 1/1/01 1.0 25 Alpha-Microbes (count)
> > Emp1_ID XYZ_ID 1/1/01 1.5 .00048 Beta-Microbes (weight)
> > Emp1_ID ABC_ID 1/2/01 5.5 2 Alpha-Microbes (count)
> > Emp2_ID ABC_ID 1/1/01 3.0 8.5 Alpha-Microbes (count)
> > Emp2_ID XYZ_ID 1/2/01 1.5 500,010 Gama-Micorbes (measurement)
> > Emp1_ID XYZ_ID 1/2/01 .25 3 People (count)
> > Emp1_ID XYZ_ID 1/2/01 .25 1 Charts (count)
> > Emp1_ID XYZ_ID 1/2/01 .25 5 Phone calls (count)
> > ...
>
> This last view clarifies a lot. Your problem essentially is the old design
> dilemma: a single heterogeneous list vs. multiple tables. I don't agree
that
> heterogeneous list is necessarily better. First, creating a new table per
> each new type of measurement is not a bigger deal than declaring a new
type
> (or should I better say "programming new type"?).

Mikito,

In the above, are you ignoring the requirement Mike gave that users may invent new statistics and disable statistics?

Having the application create new tables at the user's request means that the application must have access to a user id with resource privileges. Either the business must grant these privileges to all of the users of the application, or the application must hard-code or store a user-id/password. Any of these options could present security issues.

Not only must a new table be created when the user invents a new statistic, but the view that combines all of the tables must be replaced and the constraints re-declared.

All of the unecessary DDL will prevent static compilation (if this is necessary or desired) and will force the application to use dynamic SQL or some other form of code generation to deal with the multitude of table names for similar queries.

The application that allows users to create new statistics must either force end-users to invent unique legal names for each of the tables, or the application must generate a unique legal name that may lack meaning to users.

You have already pointed out the difficulty with integrity enforcement.

The principle of orthogonal design requires that each of these statistics tables have a unique predicate, and I am unsure what predicate you might suggest. It is not entirely clear to me that every different statistic type will have a unique domain.

Are you suggesting that the principle of orthogonal design is not a good design principle? If so, do you have counters to the examples and arguments put forth by Date and McGoveran when they proposed the principle? Received on Fri Apr 11 2003 - 22:17:20 CEST