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." <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
