Re: Database design question

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

Date: Thu, 29 Jul 2004 14:19:21 -0500

Just trying to be positive :) 

-- 
----------------------------------------------------------------------------
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 :)
"Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote
in message news:%23FxeywXdEHA.3632@TK2MSFTNGP11.phx.gbl...
> > 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: Using SQl to store aspx pages and memory problems
    ... I've seen classic ASP applications design this way that work ... database isn't some sort of magic pixie dust that solves all problems. ... > the memory increases - the slowness of the request could be dealt with by ... >> takes to get the page from SQL Server. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Query to search multiple identical tables
    ... separate from the newsgroups and the advices given in these newsgroups are ... whether you referred to making your database, with you as the developer, as ... correct and then to design easy and intuitive GUI for your users. ... know how to apply the Database Normalisation technique). ...
    (microsoft.public.access.queries)
  • RE: database options with SBS 2003 R2
    ... It was doable for the customer to design their own access database in a peer ... If you don't want to deal with a version of SQL Server for the data, ... end MDB files. ...
    (microsoft.public.windows.server.sbs)
  • Re: delete issues with ado
    ... Look for newsgroups with either the ... >> all of the data off of the sql server in the database into a Dataset. ... The updateTable web service exception gets thrown. ...
    (microsoft.public.vb.database.ado)
  • Re: A little help with access forms.
    ... This is an upsized database and it does have the upsize_ts field, however, I ... I lay no claim to this "design". ... I didn't say Access, I said .mdb file. ... get my users over to SQL server. ...
    (microsoft.public.access.conversion)