Jim Dettman <> wrote: > Stephen, > > > <<No you generate a proper table that defines the shipping charges > and it should reference the Description from lookups.key >> > > Interesting that you used the word "proper". > > The fact that the addition of a shipping charge causes you to want > to break it out into a separate table implies that you consider a > "Shipping Type" to be something that is distinctly different then a > "salutation" and hence they should not be in the same table in the > first place. The fact that at the start they have the same set of > attributes does not change the fact that they are two different > things.
What are you smoking? Shipping types are more then just a simple lookup. They include rates as well as discounts, and date ranges for their use. What part of that are you confusing with a simple TEXT description that I have been speaking of? > This would be shown more directly if had gone with option #1, > because all the attributes given could not apply to every instance in > the table (a salutation could not have a shipping charge value). > > Interesting too that you'd leave the description in the lookup > table. So your shipping type table would look like this? > > tblShippingTypes > ShippingTypeID - Autonumber - PK > DescLookupID - FK to tblLookups > ShippingCharge - Currency > > If your going that route, why not: Nope. > tblShippingTypes > ShippingTypeID - Autonumber - PK > DescLookupID - FK to tblLookups > ShippingChargeLookupID - FK to tblLookups > > and keep the shipping charges as a lookup value? Sorry but the row of data is time oriented. You get a Rate for show on your invoices, your rate, the discount amount, as well as the surcharge. See 3NF below. > In either case, you now have attributes of a given instance of some > "thing" stored in two different tables; definitely not a normalized > design. <http://www.datamodel.org/NormalizationRules.html> 1NF Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key. 2NF Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table. > A single lookup table is a shortcut plain and simple and it is not > a normalized design. Can you get away with it? Sure. Have I? Yes > just as I have used surrogate keys for many years. But I would not > claim that any of my designs are fully normalized. Stephen Russell DBA / .Net Developer Memphis TN 38115 901.246-0159 "A good way to judge people is by observing how they treat those who can do them absolutely no good." ---Unknown http://spaces.msn.com/members/srussell/ No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.10.16/914 - Release Date: 7/23/2007 7:45 PM _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
