Re: Database design question

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 15:55:57 +0100

> Perhaps in an object oriented programming newsgroup you might
> find more support.

Maybe. I have worked with a database which included this design, but after
having to work with it for a while, _even_ the C++ programmers agreed it
caused no end of problems. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message 
news:eq$mpkXdEHA.3076@TK2MSFTNGP10.phx.gbl...
>I would just say this.  The kind of design you are suggesting is going to
> cause you way more trouble than it is worth.  Every table should model one
> and only one thing.  It makes your database(, and it follows data) far
> easier to use, and far easier to understand.
>
> I understand what you are trying to do, but it is not at all what SQL 
> Server
> is designed to do.  Using SQL Server as merely a storage for text values 
> is
> a way to go, but you will get little support here for it, because many of 
> us
> have seen this kind of thing and had to deal with the clean up.
>
> I also don't want to say that this approach is never valuable. 
> Occasionally
> we have situations where we need to store properties/attributes about an
> object that is unknown at design time.   I have used this kind of 
> structure
> to store information about networking devices that have wildly varying
> values and attributes.  Using this data was kind of a pain for the most
> part, but it was the only way to store values that could not be known at
> design time.
>
> 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 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:%23XuZKpWdEHA.720@TK2MSFTNGP11.phx.gbl...
>> Hi Jacco,
>>
>> We would handle referential integrity from code only. This means that we
>> validate the user input before saving data. The lookup values we often
>> encounter will be user-defined, and it will be hard/impossible to make 
>> the
>> users specify criterias for validation. This means that a user could
>> potentially put the value "Male" into the country column, making it a
> valid
>> country lookup value, but this is not very likely... And many of the
> values
>> will be transferred from other systems, ensuring that only correct values
>> are available for registration.
>>
>> When it comes to the "20 joins" argument, it is true that you would have
> to
>> make a complex query with many joins to the same table if you need to
> search
>> on many different lookup-values at the same time. However, when selecting
>> all values on one document you would only need one join.
>> This is actually true for both solutions sketched in the original post,
> and
>> we think this is a huge benefit.
>> With many lookup-tables you would always need to join, both for selecting
>> data and for searching.
>>
>> Anders
>>
>> "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid>
> wrote
>> in message news:%23HFLWuVdEHA.2544@TK2MSFTNGP10.phx.gbl...
>> > Ask yourself, how would you enforce referential integrity in your
> design?
>> In
>> > other words, how would you prevent the code for say a country being
>> entered
>> > in the column for say gender?
>> >
>> > > Imagine the join you would have to create with 20 lookup-tables(we
> could
>> > > be
>> > > facing many more). Each of them could potentially be quite large.
>> >
>> > If you have 20 "look up columns" in one table, you still need to create
> 20
>> > joins, whether you store your lookup values in one table or 20 tables.
>> >
>> > -- 
>> > Jacco Schalkwijk
>> > SQL Server MVP
>>
>>
>
>

Relevant Pages

  • Re: Relational-to-OOP Tax
    ... It is up to you to support it or retract it. ... No, I agreed that using a database for simple create, read, ... You made the claim that using the database features will simplify ... the design relative to using OO techniques, ...
    (comp.object)
  • Re: Object Oriented Database Design
    ... I would use a relational database to contain the data. ... The relational model should support the data in base tables, ... do and the relational systems cannot. ... > from the object design. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: change field properties, reflect in query
    ... Another thing you could try is creating a new blank database and ... survey could get cranky;). ... I'm using lookup tables, validation ... it is likely you are doing something wrong with your database design. ...
    (microsoft.public.access.gettingstarted)
  • Re: Database design question
    ... The kind of design you are suggesting is going to ... to store information about networking devices that have wildly varying ... And if you are proposing to build a database where you define your database ... > country lookup value, but this is not very likely... ...
    (microsoft.public.sqlserver.programming)
  • Re: Database design tools - which do you like and what criteria do you use [SQL Editor]
    ... So far SQL Editor looks best ... Factors in Evaluating a Database Design Tool ... customer support. ...
    (comp.databases)
Quantcast