You might compromise with a nested table of name/value
pairs for "attributes" to get management off your
back.
There was a lengthy thread a month or so ago on a
similar design issue. I am sure Tim Gorman can provide
some info on his nightmare project where there was a
single table w/name/value pairs for the entire
database.
You have covered a good many of the Pros/Cons.
I can't emphasize enough how difficult it is to
denormalize this information. You end up with alot of
dups of the tables in the from clause. Really ugly
queries.
Other cons are you have to reinvent alot of your own
code to enforce bussiness rules, data types, and
uniqueness that are already provided by the database.
Bill
- "Young, Jeff A." <jayoung_at_trilegiant.com> wrote:
> Hi all.
>
> I am in the midst of designing an ERD for a usage
> system. The management
> would like to use name/value pairs to store the data
> since that would be the
> most flexible. While I agree that it would be the
> most flexible on the
> surface, I am concerned that it might not be a good
> idea. It is not clear
> at this point whether this data will be needed for
> an OLTP solution. The
> most pertinent use of this info at this point is for
> reporting.
>
> Cons.
> 1. You have to select more records to get 1 'row'
> of data since a
> name/value record would only hold 1 piece of data.
> Queries to mine the data
> would be more complicated.
> 2. No meta-data. So, a zip_code could end up being
> alpha-numeric and 57
> characters long.
> 3. Can't put on foreign keys.
> 4. Possible performance issues since more inserts
> have to be done to get a
> 'row' of data committed.
>
> Could you provide more cons (and pros) to using
> name/value pairs? Any good
> resources on the web I might look at? I've poked
> around out on the web, but
> didn't have much luck. Thanks.
>
> - Jeff
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Young, Jeff A.
> INET: jayoung_at_trilegiant.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pass
INET: wbpass_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jul 09 2002 - 14:31:04 CDT
