InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 23:17:16 GMT20http://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspx"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.Wed, 01 Jun 2011 09:47:20 GMTTodd M. Owenshttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspx[quote][b]phickey (8/25/2010)[/b][hr]"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.[/quote]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.Wed, 23 Feb 2011 11:09:48 GMTTodd M. Owenshttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspx"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.Wed, 25 Aug 2010 06:42:05 GMTphickeyhttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspxExcuse 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.Tue, 24 Aug 2010 10:28:35 GMTCarto Queryhttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspx[quote][b]rnunn (5/26/2010)[/b][hr]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.[/quote]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 - GrahamThu, 27 May 2010 04:47:16 GMTSlabberhttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspxIn 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.Wed, 26 May 2010 12:58:13 GMTrnunnhttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspxThank 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,Tue, 25 May 2010 16:29:17 GMTNicole Bowmanhttp://www.sqlservercentral.com/Forums/Topic927205-1604-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/69927/">Stairway to Database Design STEP 3: Building Tables</A>[/B]Mon, 24 May 2010 23:50:58 GMTCELKO