[thelist] Magic data table

[Matt Warden]

On Wed, 18 Aug 2004 12:44:40 -0700 (PDT), Edwin Horneij
<ed_horneij at yahoo.com> wrote:

> I think the system as designed is overkill, but I suspect the original
> developer had already built it out as an all-purpose tool. It seems
> pretty solid, but I am reluctant to trust something I don't understand
> well, and the client is impatient for me to start making the changes he
> wants, so I am going to follow my instinct and restructure the DB along
> more conventional lines.

I would suggest that you would be better off diving in and figuring
out the current structure. Unless it is keeping you from doing
something because the current structure is *unable* to support a
certain change, I would think that restructuring your DB this late in
the game would cause more problems than it would fix.

It is obviously a decision you have to ultimately make based on your
first-hand knowledge of the database and the changes needed and your
ability to comprehend the current structure, but I would suggest that
restructuring the DB would be a last resort.

Fwiw, the structure you describe seems like a failed attempt at a
rather typical design that I usually refer to as a 'custom attributes'
design. There are a lot of instances where you essentially need to add
attributes to a relation and, obviously, you want to avoid physically
altering the relation. For example, let's say we are collecting
information on customers and we have 10 customer types, each with
different relevant data. The 'business owner' has attributes first
name, last name, company, and capital. The 'housewife/househusband'
type has attributes fist name, last name, marital status, and primary
source of income. In addition to the 10 current types, more can be
added at any time.

Since you don't know what will be relevant to future customer types,
you might elect to use a custom attributes design like so (simplified
-- obviously ATTR would probably be in a M-M relationship with
CUSTTYPE, not a 1-M):

ATTR
------------
id
custtype_id
name
isactive

ATTRVAL
------------
id
attr_id
cust_id
value
datecreated

And you have basically emulated a relation of infinite degree. You can
generalize it thusly, so it can apply to any relation in the database
(instead of just CUSTTYPE):

ATTR
------------
id
fk_id
fk_relationname
name
isactive

ATTRVAL
------------
id
attr_id
fk_id
fk_relationname
value
datecreated

I mention these designs not because I endorse them (especially the
latter, which enjoys spitting in the face of E.F. Codd himself), but
only because I think they might assist you in understanding your
current design. I could be way off with why the design is the way it
is. However, you alluded to flexibility, and I agree that it seems the
only possible motivation.



-- 
mattwarden
mattwarden.com


This email proudly and graciously contributes to entropy.