 |
Fully Qualified Naming - The First Step To Normalization
Posted on 05/05/03 02:33 PM
Posted by Jerry Dennison - Head Wizard
Posts: 14815 - Loc: South Carolina, USA
Forum: Access FAQAs
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
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.
Entity = 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
Number
Date Entered
Date Completed
Item Table
Primary Key
Number
Description
Price
etc.......
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)
Quantity
Discount
(note: do not include item description or other attributes from other tables that are not specific to the work order detail entity)
--------------------
Jerry Dennison
Microsoft Office Access MVP 2006, 2007, 2008, 2009
Greenville, SC USA
"Advice is a dangerous gift. Even from the wise to the wise for all courses may run ill." - J.R.R. Tolkien, The Lord of the Rings
|
 |
| Page Jump |
|
Pages: 1
|
|
|
|
| Navigate |
|
|
|
| Page Jump |
|
Pages: 1
|
|
