Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Database Design STEP 3: Building Tables Expand / Collapse
Author
Message
Posted Monday, May 24, 2010 11:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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
Posted Tuesday, May 25, 2010 4:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:47 PM
Points: 188, Visits: 1,543
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
Posted Wednesday, May 26, 2010 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 8:19 AM
Points: 4, Visits: 36
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
Posted Thursday, May 27, 2010 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 6, 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
Posted Tuesday, August 24, 2010 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 25, 2010 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:38 AM
Points: 7, Visits: 98
"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
Posted Wednesday, February 23, 2011 11:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:50 AM
Points: 54, Visits: 423
phickey (8/25/2010)
"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.


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
Posted Wednesday, June 1, 2011 9:47 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:50 AM
Points: 54, Visits: 423
"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
Posted Wednesday, September 18, 2013 5:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:07 PM
Points: 36, Visits: 216
Slabber (5/27/2010)
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


this all true with one exception though...
if you are or want to use ORM or micro ORM that works on POCOs other than entity Framework, singular naming could be a stumble block.
for example . a PetaPoco expects that a table name is plural as it would create a POCO using tablename singular.
as in an Employees table is a collection of Employee POCOs
which make sense. it's not to say that this particular ORM can not work with singular tabelnames but you would need to do some work around for it to work properly.
I have seen this behavior with other frameworks as well.
also it could become confusing to see what are you reffereing to table,class or record if all is singular.

but this rules are not writen in stone...
Post #1495863
Posted Wednesday, September 18, 2013 6:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 1:37 PM
Points: 6,779, Visits: 1,865
Joe, this reminded me that I've neglected my use of check constraints. I'm much more likely to use a foreign key. In part because I know that those relationships are important for data quality, in part because they make reporting easier. The lack of checks is perhaps lack of attention on my part, though I'm trying to think of places where I should have used them recently - not there, or didn't see them?!

I'd love to see SSMS "fixed" to have a real expression editor and not just a text box for checks, one that encourages cross column constraints via good UI.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #1495903
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse