Stairway to Database Design STEP 3: Building Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Joe Celko » Stairway to Database Design STEP 3: Building...

Stairway to Database Design STEP 3: Building Tables

Rate Topic

Display Mode

Topic Options

Author

Message

CELKO

Posted Monday, May 24, 2010 11:50 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Comments posted to this topic are about the item Stairway to Database Design STEP 3: Building Tables

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #927205

Nicole Bowman

Posted Tuesday, May 25, 2010 4:29 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 171, Visits: 1,410

Thank you for a clear, easy to understand article. It has made me realise that I have slowly become more reliant on the front end applications I design for verifying and constraining data in my databases. This is a bad habit that I intend to break immediately!

Cheers,

Nicole Bowman

Nothing is forever.

Post #927858

rnunn

Posted Wednesday, May 26, 2010 12:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 8:20 AM
Points: 3, Visits: 31

In general, the series was good. Somes points concerning step 3. Entity names: Forest is not best. A forest includes things other than trees, i.e. frogs, bushes, grass, bugs. If the intention is to catalog trees, I do not want to allow a row which describes a beetle. Likewise, personnel is not better than employees or employee. Personnel is also more abstract and could include contract workers, interns (?), or anyone proforming a service for the organization.
Whether or not one uses the singular version or plural is a matter of the standard set by the organization and has long been a database design religious debate. As long as it is done consistently and the entity/table is well defined, it doesn't really make a lot of difference.

Rick N.

Post #928557

Slabber

Posted Thursday, May 27, 2010 4:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, August 06, 2012 1:17 AM
Points: 34, Visits: 150

rnunn (5/26/2010)

Whether or not one uses the singular version or plural is a matter of the standard set by the organization and has long been a database design religious debate. As long as it is done consistently and the entity/table is well defined, it doesn't really make a lot of difference.

And using singular table names such as Employee or Customer avoids probelms with irregular plurals and gives a more pleasing symmetry to (compound) relationship table names. It also means less editing of automatically generated entity names when using the Entity Framework if that's a consideration for you.

Cheers - Graham

Post #928886

Carto Query

Posted Tuesday, August 24, 2010 10:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 22, 2012 6:30 AM
Points: 7, Visits: 33

Excuse the newbie attempt, but I didn't see any responses to your article's final questions so here goes...

1. If we delete a deceased customer, their entry in the Customers table is deleted, thus cascading this deletion into Sales_Orders table and removing all historic record of their orders. I'm assuming this sort of immediate update would be fine in the case of the purchase of electronics, but not in something like a federal income tax system where a family estate might take years to clear the deceased's account. In this case, I would expect and update to the 'Customer' entry would take place to include 'Estate of'.

2. The question suggests that the GTIN is kept and the description is changed in Inventory, therefore all records of the purchase in Sales_Order_Details of the Lawn Gnome (LG) are changed to show the purchase of the Pink Flamingo (PF). (If that's right... it doesn't seem like a good way to handle historic records. Do I have this correct?)

Not being familiar with the GTIN, I'm a little confused by this. I would assume each individual item would have a distinct GTIN that represents it, therefore I would assume a Lawn Gnome (LG) and a Pink Flamingo (PF) would have unique GTINs. In that case, I would assume we delete the LG from Inventory, which would cascade and delete all records of them ever having been bought in the Sales_Order_Details. Then we would add the PF GTIN to the Inventory and make this available for purchase in the Sales_Order_Details.

3. I'm assuming that to discontinue a product we would delete it from the Inventory table, thus removing all record of it having been sold when it is updated into the Sales_Order_Details.

4. After the Inventory was changed in steps 2 & 3, I would assume that a customer would simply not find the LG product available to order, thus preventing the order from taking place at all. Still... it might be nice to have kept the GTIN, marked it discontinued and recommend the Cowboy Silhouette instead.

I'd be interested in hearing back on how close this response is to correct on each point.

Post #974276

phickey

Posted Wednesday, August 25, 2010 6:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:45 AM
Points: 4, Visits: 77

"Forest" is a bad replacement for "Trees" for even more reasons. The trees in my yard and the trees in your yard could both go in the "Trees" table, but they aren't part of the same forest (or any forest). A forest has a boundary (physical or legal) and many other attributes that are not merely aggregates of tree attributes. "Forest" isn't really the collective of "tree", so perhaps it's just a bad example, but using collectives that take a different form than the entity name is just too abstract, abstruse, and "clever" to be good policy. Singular or plural, names need to be obvious, accurate, and consistent.

Post #974804

Todd M. Owens

Posted Wednesday, February 23, 2011 11:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 48, Visits: 337

phickey (8/25/2010)

I could not agree more. In addition to the analogous issues on plural forms coming from irregular plurals, not every conceptual entity has an obvious and appropriate collective term.

Post #1068441

Todd M. Owens

Posted Wednesday, June 01, 2011 9:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 48, Visits: 337

"It is not always possible to decompose n-ary relationships into binary relationships." Well Celko said it, so it has to be true?

In my experience this is NOT true. Let's take his example of a Home Mortgage, with a Lender, Buyer and Seller. His example appears correct at first glance, but this is specious. He has left out several concepts of importance. A little Entity-Relationship analysis provides us with the complete answer: Mortgages require collateral, i.e., a 1) Home. A Home Mortgage is just one component of financing a 2) Home Sale; other components may include a Down Payment, a Second Mortgage, a Cash Gift (from family) and others. A more complete analysis even tells us the seller is not directly related to a mortgage at all!

Actually unresolved ternary and quaternary relationships are what cause higher level (BCNF, 4NF, 5NF) normalization issues. What Mr. Celko calls auxiliary tables (a.k.a. reference or look-up tables) do not count; it is only the one or two "entities" that the new entity depends upon that matter.

I believe Mr. Celko suffers by starting with data elements to do table design. His approach is unsurprising when one considers he is an expert in SQL DDL (and DML, etc...) and not data modeling. The very best business and data architects start with ER modeling and then apply truths about data elements, data types and domain constraints.

Post #1118134

« Prev Topic | Next Topic »

Permissions