Re: Best Data storage practice?

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

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 11/01/04 

Date: Mon, 01 Nov 2004 13:54:01 -0800

>> I have six textbox controls on my web-form that allows the user to
enter the numbers 1-47 non-duplicates in each box <<

We don't care about that stuff; this is the database, not the GUI.

>> Is it best to store the user set of six numbers in the database as..
NumID-PK, Num1, Num2, Num3, Num4, Num5, Num6 <<

Almost. A column is not a field; it can have constraints. You need to
enforce your business rules here and captrue the important data, more
like this:

CREATE TABLE LotteryDrawings
(drawing_date DATETIME DEFAULT CURRRENT_TIMESTAMP
       NOT NULL,
 user_id CHAR(15) NOT NULL,
 PRIMARY KEY (user_id, drawing_date),
 num_1 INTEGER NOT NULL
       CHECK(num_1 BETWEEN 1 AND 47),
 num_2 INTEGER NOT NULL
       CHECK(num_2 BETWEEN 1 AND 47),
 num_3 INTEGER NOT NULL
       CHECK(num_3 BETWEEN 1 AND 47),
 num_4 INTEGER NOT NULL
       CHECK(num_4 BETWEEN 1 AND 47),
 num_5 INTEGER NOT NULL
       CHECK(num_5 BETWEEN 1 AND 47),
 num_6 INTEGER NOT NULL
       CHECK(num_6 BETWEEN 1 AND 47),
 CONSTRAINT sort_numbers
 CHECK ((num_1 < num_2)
         AND (num_2 < num_3)
         AND (num_3 < num_4)
         AND (num_4 < num_5)
         AND (num_5 < num_6))
);

Notice that I am making you sort the numbers to make them easier to
handle later. This is an easy job the front end, but you can do it in
one INSERT INTO statement if you wish.

>> NumID-PK, NumSet {1-PK, 1, 35, 23, 5, 4, 17 respectfully stored
separated by commas in the column in the database}? <<

NEVER, never do this! Look up First Normal Form (1NF) in the beginning
of any basic RDBMS book.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Naming conventions for special database objects
    ... I am currently doing a review of my personal database naming ... InsteadOfInsert_Orders for multi-purposed triggers. ... Default constraints ... How to name a primary key if not primary key? ...
    (comp.databases)
  • Re: Slow data entry form
    ... Create database constraints (PRIMARY KEY, UNIQUE, validation ...
    (microsoft.public.access.formscoding)
  • Re: constraints and primary keys
    ... I am after any feedback on the concepts of primary key, constraints, unique ... might want to pick up a book on basic database theory. ... fact be "candidate keys" which means that there is proper subset of ...
    (comp.databases.theory)
  • Re: SQL Server error when attempting to delete a record
    ... What version of SQL Server have you got? ... recordto be affected and execute this command based on the Primary Key ... Script the table's definition to a file (including constraints, indices, ... of the database, factors like that. ...
    (microsoft.public.sqlserver.server)
  • Re: A real world example
    ... If a constraint is defined in terms of successive states of a database, ... Yes, that's all facts are, though constraints mean that your facts are ... value of a candidate key determines the values of all other attributes, ... The frame of reference for a candidate key is a ...
    (comp.databases.theory)