Re: object model to table design mapping problem

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 09:55:09 -0700

>> Given the object model, I want to design the database tables. <<

Many years ago, the INCITS H2 Database Standards Committee(nee ANSI X3H2
Database Standards Committee) had a meeting in Rapid City, South Dakota.
We had Mount Rushmore and Bjarne Stroustrup as special attractions. Mr.
Stroustrup did his slide show about Bell Labs inventing C++ and OO
programming for us and we got to ask questions.

One of the questions was how we should put OO stuff into SQL. His
answer was that Bells Labs, with all their talent, had tried four
different approaches to this problem and come the conclusion that you
should not do it. OO was great for programming but deadly for data.

I have watched people try to force OO models into SQL and it falls apart
in about a year. Think in relational terms instead.
  
Let's get collective or plural names on the tables, since they model
sets, make the data element names consistent (why did you have both
"name" and "school_name" for the same thing??) and pick sizes that match
the USPS rules for address labels.

These are entities and they are modeled in their own tables:

CREATE TABLE Schools
(school_name CHAR(35) NOT NULL PRIMARY KEY,
 principal VARCHAR(15) DEFAULT '{TBD}' NOT NULL,
 ..);

CREATE TABLE Courses
(course_name CHAR(35) NOT NULL PRIMARY KEY,
 credits INTEGER NOT NULL,
  .. );

This is a relationship, and it is modeled in its own table:

CREATE TABLE Catalog
(course_name CHAR(35) NOT NULL
  REFERENCES Courses(course_name),
  ON UPDATE CASCADE
  ON DELETE CASCADE,
 school_name CHAR(35),
   REFERENCES Schools(school_name)
     ON UPDATE CASCADE
     ON DELETE CASCADE,
 PRIMARY KEY (course_name, school_name),
 ... );

Doing stuff like having a column take on a name based on which table it
is in (i.e. "FOREIGN KEY(schoolname) REFERENCES School(name)") is a
horrible design flaw that completely destroys your data dictionary.

If you never built a data dictionary in the first place, try to catch up
before you are toally screwed -- or update your resume and run for the
hills.

--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.

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

Relevant Pages

  • Re: String Search
    ... Sample data is also a good idea, ... (proj_nbr INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... REFERENCES Projects ...
    (microsoft.public.sqlserver.programming)
  • Re: Design for Category Feature
    ... surname VARCHAR NOT NULL, ... REFERENCES ContactCategories ... ON UPDATE CASCADE ... PRIMARY KEY ); ...
    (comp.databases)
  • Re: Selecting several types
    ... Please post DDL, so that people do not have to guess what the keys, ... NOT NULL PRIMARY KEY, --let's use GTIN ... ON UPDATE CASCADE, ... REFERENCES ItemTypes ...
    (comp.databases.ms-sqlserver)
  • Re: Cascading update on primary key
    ... primary key depends on one of the 2 secondary tables. ... (mom CHAR(5) ... REFERENCES Mothers ... ON UPDATE CASCADE, ...
    (microsoft.public.sqlserver.programming)
  • Re: How do I discover a tables primary key?
    ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)