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. 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: tblShippingTypes ShippingTypeID - Autonumber - PK DescLookupID - FK to tblLookups ShippingChargeLookupID - FK to tblLookups and keep the shipping charges as a lookup value? In either case, you now have attributes of a given instance of some "thing" stored in two different tables; definitely not a normalized design. 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. Jim. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 24, 2007 1:25 PM To: [EMAIL PROTECTED] Subject: RE: [NF] Consulting saga continued or Why Be Normal? Will answer in line. -------- Original Message -------- Subject: RE: [NF] Consulting saga continued or Why Be Normal? From: "Jim Dettman" <[EMAIL PROTECTED]> Date: Tue, July 24, 2007 7:52 am To: <[email protected]> Stephen, <<Bull!>> Like I said, I've been down this road<g>. <<Trust me they are all Normalized to a single table>> No, they are not. Let me ask you a question. Let's say you currently have salutations and shipping types in a single lookup table. Your are asked to add a shipping and handling charge based on the shipment type (ie. UPS ground, Air, etc). Do you: 1. Add a column in your lookup table to hold the S&H charge and leave it NULL for every lookup that is not of the type "Shipping" ------------ No you generate a proper table that defines the shipping charges and it should reference the Description from lookups.key Lookup values FedEx Ground FedEx Overnight bla bla bla ------------------ 2. Do you break out the shipping lookups into a separate table. If so, why? ------------------- Yes because the numeric values will be date defined, hold the rate, the discount amount, and surcharge rate that is in effect for that starting to ending date. Also one of the things I'd like to add is that you must keep in mind is that relational theory is actually a branch of mathematics. It doesn't exist because of computer database systems, but it is applied to them. To make that clearer, if I wanted to track data using nothing more then a chalkboard, I could do so and still apply relational theory to the process. I agree. I am extracting to the smallest common denominator a list of different lookups. They all follow set theory in that they are a sub group of a larger group in an identical layout. I don't alter the layout, but will make a new table to fit it. Are your contact's details in a single master table with corresponding email table and phone tables associated? Or do you just slam out more columns in the master table and you don't need detail tables? It's only disk space or is it organized space with similar data contained in the same table? ------------------------------ -----Original Message----- From: profoxtech-bounces @leafe.com [mailto: profoxtech-bounces @leafe.com ] On Behalf Of Stephen the Cook Sent: Monday, July 23, 2007 8:48 PM To: profoxtech @leafe.com Subject: RE: [NF] Consulting saga continued or Why Be Normal? Jim Dettman <> wrote: > I just went down this road not too long ago. All I can say is; > it's nice to be in control<g>. > > Strictly speaking, a single table for lookup values is not > normalized. > What your doing with the procedures below is to move part of the > normalization into your code rather then having it in the database > design. Bull! Dude, I have 5 set up tables that are all identical. Pkey, Description, and other associated crap for table correctness. Trust me they are all Normalized to a single table, and to have table for 4, 3, or 7 rows each is just to funny. IMO. 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.14/912 - Release Date: 7/22/2007 7:02 PM [excessive quoting removed by server] _______________________________________________ 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.
