Re: New bie question about NULL

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Peter (zlxmqyt_at_sina.com)
Date: 11/25/04 

Date: Thu, 25 Nov 2004 09:05:40 +0800

Louis,

Now , I think I've got it. My table should be divided into 2 tables: One is
to meet the requirement I have known,the other is to meet what I haven't
know fully. And it become so flexible.

Thanks for your patience.

"Louis Davidson" <dr_dontspamme_sql@hotmail.com>
Wrote:#F4NZHk0EHA.1408@TK2MSFTNGP10.phx.gbl...
> You still seem to be confusing some things.
>
> > But I don't know how many columns my user requires.
>
> Does this mean you aren't finished design yet? Or that you are confused
> about columns and rows?
>
> Columns are scalar attributes. Let's say you have a person table, you
might
> have first_name, last_name, height, weight, hair_color, etc. Not knowing
> the number of columns the user requires indicates that you don't know how
> many of these they want or if they want more, waist_size, eye_color, or
> whatever.
>
> To me, from the limited information you have shared, you mean you don't
know
> how many data points they want. So you are trying to take care of it by
> having columns like measure1, measure2, etc. This is not a good design.
>
> This is a multi-valued dependency, and needs to be broken out into a
> different table. Something along the lines of:
>
> measure
> ---------
> personId
> measureName
> measureValue
> key (personId, measureName)
>
> Now you have a flexible structure to deal with, and it does not require
> forethought as to an exact number.
>
> --
> -------------------------------------------------------------------------- 

--
> Louis Davidson - drsql@hotmail.com
> SQL Server MVP
>
> Compass Technology Management - www.compass.net
> 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 may be ignored :)
>
> "Peter" <zlxmqyt@sina.com> wrote in message
> news:%23siFfTe0EHA.2540@TK2MSFTNGP09.phx.gbl...
> > David,
> >
> > I mean I know the attribute of column my user requires, all the columes
> > have
> > same data type 'real'.   But I don't know how many columns my user
> > requires.
> > So I wonder it will cause disk space wasted if I create a table that
have
> > too many spare columns.
> >
> > Peter
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org>
> > Wrote:TJ6dnRFQKIvdnTncRVn-tg@giganews.com...
> >> > I can't determine how many columns my user will use
> >>
> >> How *many* columns? Surely you mean you can't determine *what* columns
a
> >> user will require. Tables are not arrays and columns represent
particular
> >> attributes not placeholders in a list. How can you create a column if
you
> >> don't know what the attribute is supposed to be? How could you
validate,
> >> update or use such a column?
> >>
> >> If it isn't possible to determine the user's requirements now then I
> > suggest
> >> you wait until it is possible to determine them and add new columns at
> > that
> >> point. Yes, columns with NULLs will take up space in a row (although
> >> space
> >> seems to be the least of your problems).
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Mail Merge to SQL Server without using an external ODC
    ... but when the source changed to SQL Server it quit ... "Peter Jamieson" wrote: ... the interface) would be to distribute the *.odc file for the connection ... The distribution path you describe would require an installer, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2003/Access2000/SQLSVR
    ... SQL server being where the data is held and this is accessed through a MS ... entire database and that may be say a record for Berkshire. ... "Peter Jamieson" wrote: ... replaced with the first record on the table in use. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: OpenDataSource SQL Server xpress problem
    ... Thanks a lot Peter. ... all the examples for Access files and none for SQL Server ... Open Data Source dialog, selecting the "Microsoft SQL Server" option, then ... 'Dim strConnect = System.Type.Missing ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Select command with multiple tables
    ... it becomes difficult to write each and every field in the query. ... David Portas, SQL Server MVP ... State what version of SQL Server you are using and specify the content ... will it be inefficient to use select * from .. ...
    (comp.databases.ms-sqlserver)
  • Re: Select command with multiple tables
    ... is slow because it requires extra work by the server to retrieve the ... David Portas, SQL Server MVP ... State what version of SQL Server you are using and specify the content ... A user needs a few more fields in the database and adds them to a table ...
    (comp.databases.ms-sqlserver)