Re: Database design question

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 09:24:53 -0500

I read (over) the article, and what he is talking about is a single code
lookup table (still a bad idea, but ) not a single table to hold all of the
attributes of some thing.

If you are only going to do what you were suggesting on "extended"
attributes, then it is a viable thing to do. I would just suggest that you
resist the temptation to make everything an extended attribute, and only use
them to allow clients to extend your base design. Having a property table
is (in my opinion) far better than having tables that have userdefined1,
userdefined2, userdefined3 etc, tacked on the end of every table to support
later needs. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Anders" <reply_to_group@news.com> wrote in message
news:%23VFrjzidEHA.1184@TK2MSFTNGP11.phx.gbl...
> Thank you for thourough answers. Below some mixed comments on replies.
>
> I agree with many of your comments, but the part we try to cover is where
we
> dont know the details of the lookup data.
>
> Yes we know that many/all customers will have things like vendors,
accounts,
> departments, projects, employees etc. etc.
> So potentially we could define these tables in our database, keeping it
> static.
>
> What do we then do with the more "unknown" user-defined lookup data. It
will
> be similar in a way(but not the same), they will probably have an
> id(varchar) of some sort, and a value with x number of additional
attributes
> varying from type to type.
>
>
> >> Imagine the join you would have to create with 20 lookup-tables.. <<
>
> <Anith>
> >>>>Very common scenario in reasonable enterprise level systems. What is
the
> >>>>concern here? For simplicity, opt to use a view, if and when required.
> </Anith>
>
> The concern here is performance mainly on selects.
>
> >> The alternative with many columns would potentially need indexes on
> all/most columns since each of the values could be a search criteria for
our
> users. <<
>
> <Anith>
> >>>>Perhaps, not in all/most columns. Once you learn the pattern of the
> query
> >>>>expressions, you can tune the schema without bloating the physical
> >>>>structures with indexes everywhere
> </Anith>
>
> Tuning per installation is indeed the best way, but we need to give a good
> "default" tuning. And we supply search pages where users can search on all
> values(they want to), so they will potentially need indexes on all
columns.
> I realise there is no such thing as a good default tune, but at least some
> default settings for small/medium installations.
>
>
> <Louis>
> However, for things like vendors, and thing that you can specifically
> define, this sort of construct is, from a SQL-based view, a very bad idea.
> And if you are proposing to build a database where you define your
database
> in metadata and store the data in these not predefined buckets rather than
> building a static, well structured, normalized database, you will get
little
> support here.  Perhaps in an object oriented programming newsgroup you
might
> find more support.
> </Louis>
>
> Perhaps this is where the real problem is :-) A bunch of OO programmers
> trying to transfer OO into the DB :-D
>
> Anyways, thanks to everyone replying so far. The reason for posting was
> ofcourse that we were unsure of the model, and it seems like we were right
> in feeling that way when looking on the response ;-)
>
> Btw: Found this article with info on the alternative approach mentioned in
> the original post.
> For those that feel like reading:
>
http://www.sqlservercentral.com/columnists/lPeysakhovich/lookuptabledesign.asp
> Think you need to register(free) to read the article.
>
> Anders
>
>

Relevant Pages

  • Re: LDAP Performance (long)
    ... Cache the slapd's internal database lookups in slapd memory. ... The first is the new TAG:key lookup, ...
    (comp.mail.sendmail)
  • Re: Same database or another?
    ... that I have used the lookup wizard provided. ... if I choose to add tables to database that have absolutely no relation to ... >> work toward the capture of the feral animals. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Lookup Tables and Field Validation Rule Properties
    ... I am in the process of designing my first database and plan to use this ... employees data and track down employees personal info, ... The tables in my original design contained many lookup fields but after ... The other question I asked was, instead of setting the above validation in the table level, can't I implement it in the Form Level. ...
    (microsoft.public.access.gettingstarted)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... more than a descriptive lookup will get confusing, ... > be found in seperate records of a code file. ... > each value-pair corresponding to one record (adding that if you prefer ... > database can accurately define my data. ...
    (comp.databases.pick)
Loading