X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Closed TopicStart new topic
> Fully Qualified Naming - The First Step To Normalization    
Jerry Dennison
post May 5 2003, 01:33 PM

Head Wizard
Posts: 14,857
Joined: 31-January 00
From: South Carolina, USA

Here is a copy of a post I gave that explains how to get to a normalized structure using a technique I call Fully Qualified Naming. I hope it is of some help.
ntity = think of this as something (real or virtual) that you can hold in your hand (if your hand were big enough) that cannot be broken down into anything smaller without destroying it's integrity. For example, a car is an entity, as such it is also made up of smaller entities (i.e. tires, wheels, engine) which could in fact be made up of even smaller entities and so on. Each of these entities can be described in some way, these descriptions are called attributes. In our car example, an attribute might be it's VIN number, color, make, model, year made, etc. These attributes must describe the entity, only the entity, and the whole entity. (one of my favorite sayings is "All attributes must describe the key, the whole key, and nothing but the key", key is the same as entity but is more specific in that it is for a single entity). To further define what is a valid attribute, let's look at what isn't: sold to. Why would the person you sell a car to not be an attribute of the car? Two primary reasons are you can sell the car to more than one person, and who the car belongs to or who may be driving it that day does not describe the car.
Attribute = a feature or description that is specific to an entity. I've already given examples of attributes above, so let's examine a relatively easy way to "build" valid attributes. Fully Qualified Naming. This is the easiest and best method for helping you define your entities and their relevant attributes. A fully qualified name for an attribute includes everything the attribute is describing. I'll use your Work Orders as an example:
WorkOrder ID
WorkOrder Number
WorkOrder Date Entered
WorkOrder Date Completed
WorkOrder LineItem Item ID
WorkOrder LineItem Item Quantity
WorkOrder LineItem Item Price
WorkOrder LineItem Item Discount
WorkOrder LineItem Item Discription
I've separated the two entities into their "natural" groups but name. You could call LineItem OrderDetail. You'll notice that a third group appears, called Item. This is in fact another entity that has emerged and should be in it's own table and the OrderDetails (LineItems) is really an intermediate table that helps define a many-to-many relationship between WorkOrder and Items. The final structure would then look something like this:
WorkOrder Table
Primary Key
Date Entered
Date Completed
Item Table
Primary Key
WorkOrderDetail Table
Primary Key
WorkOrderID (foreign key link to WorkOrder)
ItemID (foreign key link to Item)
Price (note: this would be ok to store duplicate values as prices are subject to change and should be specific to the order detail)
(note: do not include item description or other attributes from other tables that are not specific to the work order detail entity)
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    5th June 2020 - 02:53 PM