Working on design tutorial (1 Viewer)

Kraj

Registered User.
Local time
Today, 22:34
Joined
Aug 20, 2001
Messages
1,470
I've been picking at this and wouldn't mind some feedback. I thought it might be a good idea to compose a tutorial on the basics of table design. This particular topic seems to be one where people either have a hard time understanding it or haven't even been introduced to the topic. I've also noticed a lot of (now lost) posts that try to explain concepts like good table design and normal forms. So here is what I have so far of my table design tutorial. The intention is to cover design steps that should occur prior to sitting down at a computer.

Tutorial: How To Create Your Access Tables

1. Understanding Your Needs
2. The Thought Process: Relational Databases and You
3. Normalization
a. 1st Normal Form
b. 2nd Normal Form
c. 3rd Normal Form
4. Table Definition
a. Primary Key

1. Understanding Your Needs

The first step in creating a database is to know what you need your database to do. You should have a clear idea of what problem you are trying to solve or what role the database will have in your activity. You should also have a reasonable idea of what Access is capable of doing (*If you are unfamiliar with the basics of Access’ capabilities, you should start with basic tutorials or obtain a beginner’s guide of some sort). Once you have these factors in mind, you are ready to grab a pad of paper and a pencil.

2. The Thought Process: Relational Databases and You

To effectively design a table in Access, you have to know how the tables work or you’ll get nowhere. We’ll start with a definition of “relational database”:

“A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.”

Helpful, right? Simply put, a relational database is a bunch of tables with information in them. Sometimes the same information is stored in more than one table, and those tables are related to each other because of that common information.

An example – Let’s say you create a database that stores a name, home address, and car(s) owned. You make two tables, one for the address and one for the cars. They might look something like this:

Table 1
Name Address City State Zip
Eric 1234 Elm St. Chicago IL 60601
Stan 5678 Oak St. Chicago IL 60602

Table 2
Name CarModel Mileage
Eric Testarosa 20,000
Eric S-575 10,000
Stan S-575 5,000

Since “Eric” appears in both tables, the database will match “Testarosa” and “S-575” with “1234 Elm, etc.” based on the common element, “Eric” and vice versa.

Additionally, you may have a table that stores information about cars. It might look like this:

Table 3
CarModel Make
Testarosa Ferrari
S-575 Lotus
Accord Honda

In this example, Table 2 is used to join the information in Table 3 with Table 1. You now know that there is a Ferrari with 20,000 miles on it parked at 1234 Elm St. in Chicago because you know Eric lives at 1234 Elm St. (Table 1) and he has a Testarosa with 20,000 miles on it (Table 2), which is made by Ferrari (Table 3). The connections between three tables are known as “relationships”.

3. Normalization

You may be asking yourself at this time, “Why can’t you just put Ferrari and Lotus in the same table with the Eric?” At first glance it seems like a good idea that makes things a whole lot simpler and, when you’re dealing with very small sets of data and very simple databases, it is. (If this is the case, though, then an Excel spreadsheet might be a better option for you.) However, when you start working with large amounts of information, complex data, or queries, many problems arise if you do not follow a certain standard for efficient and stable table design. This standard is called “normalization” or “normal form”. There are many online resources available that discuss normalization in great detail so I will not do that. I will instead provide the essential basics.

3.a. 1st Normal Form

A database is said to be in 1st Normal Form when no records have repeating data groups. This means two things: each field should contain one unique value (ie., a ‘CarModel’ field should not contain the data “Ferrari, Lotus”); and each record should not have more than one field to store the same kind of data (ie., the records in Table 1 should not have fields such as ‘CarModel1’, ‘CarModel2’, etc.).

There are two reasons to eliminate repeating data groups. First, if you wish to search your database for a certain data, it is much easier to do so when the data is in a field by itself. Addresses are a prime example of this. If your address field says, “1234 Elm Street, Chicago, IL, 60601, USA), a simple query for everyone in Chicago will not find this record.

The second reason for eliminating repeating data groups is efficient use of space. Continuing with the address example, say we want to store both a home and business address for each person. If we put both addresses in the same record, we might have the following fields: Number, Street, St/Ave/Blvd/etc, City, State, ZIP, Country and BusinessNumber, BusinessStreet, BusinessSt/Ave/Blvd/etc, BusinessCity, BusinessState, BusinessZIP, BusinessCountry. If every entry in your database utilizes all those fields then you’re fine, but if some entries only have one address your wasted space will add up very quickly. (** See section 4xxxx)

To solve repeating data groups, make a new table with the repeating fields and an appropriate field from the first table (usually its primary key).

3.b. 2nd Normal Form

A database is said to be in 2nd Normal Form if all the fields in a record are fully dependant on the primary key. This generally applies only to tables with a composite primary key. (**See section 4.a. for more on primary keys.)

In the above example, Table 2 has a composite key; both the “Name” and “Car” fields combine to make a unique record. The “Mileage” field belongs in this table because only Eric’s Lotus has 10,000 miles on it. On the other hand, if we put the “Make” field from Table 3 into Table 2 it would violate 2nd Normal Form because all Testarosas are made by Ferrari; it doesn’t matter who owns it. Therefore, “Make” is dependent on “CarModel” but not on “Name”, so “Make” should not be in a table where “Name” is part of the key.

I know the focus of these forums is to get help after you already have a firm grasp of Access, but I have tutored enough of my own classmates to know that sometimes the basics are really confusing when your teacher sucks. So I think it's worthwhile to throw a bone to students and beginners in such a situation. I thought the final form could be as a thread where people could post questions and updates; anyone who contributes would be credited as an author.

With that in mind, does this look like it will be useful? Does anyone have feedback on how I've explained things, important points and/or topics I should include, etc.? Keep in mind this is far from the final form; I have plenty more to write on normalization and the writing in general needs to be tightened up and made more consistent. But if nobody here thinks this is a good project then I'd like to know so I don't waste any more time.
 
Last edited:

WindSailor

Registered User.
Local time
Today, 14:34
Joined
Oct 29, 2003
Messages
239
I think that this is a GREAT idea, and a very important subject.

The subject 'Normalizing a Database' is a HUGE issue, even if you develop database programs outside of Access.

Will I use it? Absolutely!

A lot of people like myself are self taught in Access and sometimes develop bad habits (Edit- I have to say thanks to some very good books that have helped along the way!).

Should I tell how many times I have started a project get half-way through the database design and then go back and re-arrange it? Definitely more than I care to count.

I have always believed that points of interest should be shown with multiple examples. This would be very advantageous for everyone to see how the Normal Form levels would apply to a Book application, Geography application, Sporting Event application, Accounting application etc.

I would even consider writing about Fourth Normal form just to show what that step entails and just how much of an impact that would have.

The other issue I have had people make suggestions to me is on the max number of foreign keys in a table which will effect on how your application will handle updates with foreign key constraints etc. On this subject I intend to do a little more research and refine things a little.

Normalizing a database is honestly still a struggle for me and any additional information or other insights on this is always appreciated.
 
Last edited:

Bat17

Registered User.
Local time
Today, 22:34
Joined
Sep 24, 2004
Messages
1,687
I think that this is a good idea too :) a good percentage of posts need pointing to Normalisation as the question is realy asking for workarounds because of bad design. Perhaps when it is finnished it can be stuck at the top as a reference.

Peter
 

The Stoat

The Grim Squeaker
Local time
Today, 22:34
Joined
May 26, 2004
Messages
239
Hi Kraj.

Yep an excellent idea. I have been thinking about a similar thing for systems analysis for some time. The flip side of problems with table design and normalization is the information [for want of a better word] being analysed doesn't necessarily reflect the real world system the DB is supposed to be modelling. Your "understanding your needs" is a big topic and the out-put of correct systems analysis can or should give you all of your primary relationships, your tables and their contents. Normalization "simply" makes sure that they are correctly structured to work in a relational database environment.
I'd go so far as to say that even if you are creating a DB from an existing flat file model that purports to be correct, it's worth checking what the function really is and if it has changed since it was designed. I've come across a number of spreadsheets that have associated paper systems because the spreadsheet fails to meet the real needs of the user when it could have easily done so. Creating a DB on the basis of the spreadsheet would have just compounded the problem even if all of the normalization procedures were correct.

Systems analysis is a huge topic but if your interested i would be more than glad to try and write a simple introduction with useful sources.

TS.
 
Last edited:

Kraj

Registered User.
Local time
Today, 22:34
Joined
Aug 20, 2001
Messages
1,470
Alrighty, I will continue to work on it then. Hopefully I'll get some input from folks like SJ and Pat Hartman who are a hell of a lot more knowledgable than I am.

Stoat, the more the merrier :) You're absolutely right and a small paragraph doesn't even come close to doing the "Understanding Your Needs" topic justice. Anything you can add to flesh that out would be fantastic.
 

KenHigg

Registered User
Local time
Today, 17:34
Joined
Jun 9, 2004
Messages
13,312
Before I do a mission critcal db, I do a bit more planning. Like doing a business process map, scope, etc. Once the owners / users buy off on these, this is what drives the data modeling, gui, etc...:)
 

Len Boorman

Back in gainfull employme
Local time
Today, 22:34
Joined
Mar 23, 2000
Messages
1,930
Go along with Ken on the planning side.

First point

Understand what an Entity is. A thing about which you wish to record data. The Attributes

So first a Conceptual Diagram that shows entities and their attributes.

Next think about the relationships. M:M, 1:M and 1:1

Now comes normalisation.

Data type definition etc

A great deal of the time I believe people decide too early what tables they will have instead of firstly identifying what they are going to consider and therefore the limits of their application.

Len
 

KenHigg

Registered User
Local time
Today, 17:34
Joined
Jun 9, 2004
Messages
13,312
This thread and Survey db thread seem to be complementary so I thought I would cobble this together: (See attached) What do you all think? Any more (or less), for the features? The ones I've list are deal breakers from my perspective (meaning the db must address these needs). I'm sure there are a plethora of nice to have features - But it would still be good to hear / collect & list them all...
 

Attachments

  • surveyDB.txt
    871 bytes · Views: 970

x0reset

Registered User.
Local time
Today, 14:34
Joined
Nov 14, 2005
Messages
52
I have been working with Access and database programing for about 6 years now, and I still don't know what fourth and fifth normal form is. Anyone have the time to jot out a brief definition/description for these and any other advanced levels of normalization?
I am hoping this might point me towards a more elegant solution for many-to-many self referencing tables (prerequisite data integrity for example), a problem I have run into several times over the years and to which I have never been able to find a satisfactory solution.
 

WindSailor

Registered User.
Local time
Today, 14:34
Joined
Oct 29, 2003
Messages
239
From the book "Special Edition Using MS Access 2003" by Roger Jennings...

Third Normal Form: Requires that data in all non-key fields of the table be fully dependent on the value of the primary key and describe only the object that the table represents. In other words, make sure that the table doesn’t include non-key fields that relate to some other object or process and includes non-key fields for descriptive data that isn't contained in another related table (all non-key fields are mutually independent), and not contain any expressions or calculated fields.

Fourth Normal Form: Requires that tables not contain fields for two or more independent, multi-valued facts. Loosely translated, this rule requires splitting tables that consist of lists of independent attributes.

Fifth Normal Form: Involves further reducing redundancy by creating multiple two-field tables from tables that have more than two foreign keys.

I have had more than a couple of people warn me about having too many foreign keys in a table, which also prompted the question would splitting the tables also help in preventing concurrency errors (certainly seems like it)... and is it worth the effort?

Because it looks like a lot of time is going to be required to take it to those levels (Fourth and Fifth Normal Form) and would taking it to those levels also make your project easier to support backward compatibility through future changes and upgrades?

This is why I am interested in this.

--Edit-- revised 3NF...
 
Last edited:

WindSailor

Registered User.
Local time
Today, 14:34
Joined
Oct 29, 2003
Messages
239
I checked “Access 2002 Developers Handbook” and their point of view was that all of that extra effort to achieve Boyce/Codd, Fourth and Fifth Normal form gave you little added value over 3NF.

They went on to quote C. J. Date, on the principles of database design: “nothing more than formalized common sense.”
And that Database design is more of an art than science.

They also came up with the following suggestive reads:

“For a more detailed discussion of database design, we suggest you read ‘An Introduction to Database Systems, Volume1’ by C. J. Date (Addison-Wesley); ‘SQL and Relational Basics’ by Fabian Pascal (M&T Books); or ‘Database Processing: Fundamentals, Design, and Implementation’ by David M. Kroenke (Macmillan). Or, if you prefer a lighter, less academic approach, we suggest ‘Database Design for Mere Mortals’ by Michael J. Hernandez (Addison-Wesley)".
 
Last edited:

workmad3

***** Slob
Local time
Today, 22:34
Joined
Jul 15, 2005
Messages
375
I noticed that no one has put in about Boyce Cod Normal Form (BCNF). The basic idea of it is that you split tables so that there are no candidate keys left in each table.

I remember my database lecturer used to use a catch phrase for 1nf, 2nf, and 3nf. The data has to rely on The key(1nf), the whole key(2nf) and nothing but the key(3nf).

The technical description for 3nf (its not up yet that I saw) is that it has to be in 2nf and no table is allowed to have any transitive dependancies on the key.

It would also be a good idea to mention that although higher normal forms exist (I think its up to 7 or 8), most applications only require up to 3, and if you are writing something that relies really heavily on performance, it is possible to increase performance by dropping down to 2nf or possibly even 1nf (never drop below that though.... unless you are doing esoteric things involving object databases)
 

WindSailor

Registered User.
Local time
Today, 14:34
Joined
Oct 29, 2003
Messages
239
Two excerpts from the links above that made an obvious distinct impression upon me about ‘Normalizing a Database’ of which are on opposite sides of the spectrum as far as ‘Normal Forms’ is concerned are:


1)
With a properly normalized database, changes to data over time are easily handled with a simple edit. Improperly normalized databases often include programming or queries to make changes across multiple records or tables. This not only requires more work to implement, it opens the possibility that the data becomes inconsistent if the code or queries don’t execute or if there’s a bug.

Records are Free, New Fields are Expensive

Databases should be designed so that over time, you simply add new records. Database tables are designed to hold huge numbers of records. However, if you find you need to add more fields, you probably have a design problem.

-----Database Normalization Tips-----by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

2)
Domain/Key Normal Form
There is one last normal form, the Domain/Key Normal Form (DKNF). Although we will not be explaining the details of this form, we mention it in our discussion of normalization for the sake of completeness.

This form was proposed by IBM's Ron Fagin in his paper "A normal form for relational databases that is based on domains and keys." In a pretty interesting argument, Fagin asserts that we don't really need functional dependencies to define our tables. As long as we know what keys are to be used, and what kinds of restrictions (constraints) we have on the values that may be placed in our columns (domains), we can prove that the table is in at least 5NF and will not suffer from insert or deletion anomalies.

To make Fagin's argument even more interesting, his proof is based on tables in 1NF. Specifically, he gets into how a 1NF table is really in DKNF if, by enforcing its domain dependencies ("entries in this column must belong to this set of values") and its key dependencies ("this entry is a unique identifier of rows"), every constraint of the table must be enforced, thus preventing data anomalies. If a 1NF has no data anomalies, then it must also be in 5NF

------Normalization Rules - Beyond The Third Normal Form----- by Jim De Carli
http://www.curlingstone.com/Normalization2.html



Does this remind you of the papers that state “Read in entirety first…”? :)

I will update my previous post if I find more links that I think will be helpful, and will bow out for now…
Hope this helps.
 
L

lippyloppy

Guest
The bit that worked for me was make another table

Kraj said:
I've been picking at this and wouldn't mind some feedback. I thought it might be a good idea to compose a tutorial on the basics of table design. This particular topic seems to be one where people either have a hard time understanding it or haven't even been introduced to the topic. I've also noticed a lot of (now lost) posts that try to explain concepts like good table design and normal forms. So here is what I have so far of my table design tutorial. The intention is to cover design steps that should occur prior to sitting down at a computer.



I know the focus of these forums is to get help after you already have a firm grasp of Access, but I have tutored enough of my own classmates to know that sometimes the basics are really confusing when your teacher sucks. So I think it's worthwhile to throw a bone to students and beginners in such a situation. I thought the final form could be as a thread where people could post questions and updates; anyone who contributes would be credited as an author.

With that in mind, does this look like it will be useful? Does anyone have feedback on how I've explained things, important points and/or topics I should include, etc.? Keep in mind this is far from the final form; I have plenty more to write on normalization and the writing in general needs to be tightened up and made more consistent. But if nobody here thinks this is a good project then I'd like to know so I don't waste any more time.

What really helped me understand your explanation was PUT THE REPEATED DATA IN ANOTHER TABLE, then light dawned!!! No one has ever put it like that befroe Thanks
 

KenHigg

Registered User
Local time
Today, 17:34
Joined
Jun 9, 2004
Messages
13,312
lippyloppy said:
What really helped me understand your explanation was PUT THE REPEATED DATA IN ANOTHER TABLE, then light dawned!!! No one has ever put it like that befroe Thanks


Sorry to hi-jack the thread...How in the world did you come up with 'lippyloppy'?
 

hotski

CT Web Site Design
Local time
Today, 15:34
Joined
Mar 29, 2006
Messages
16
This Post

Wow I am impressed this is awsome and you are in my backyard, I feel right at home here..I am studying this post every word. I want to say Thank you for the information..
 

Kraj

Registered User.
Local time
Today, 22:34
Joined
Aug 20, 2001
Messages
1,470
Well thank you and lippyloppy (I love that name :p ) very much. If there is anything unclear of confusing to you, let me know.

This is certainly some reinforcing motivation for me to work on it. :)

I've made some changes and such. I'm pretty happy with where it's at. What to you all think at this point?
 

Attachments

  • Tutorial.pdf
    94.1 KB · Views: 630
Last edited:

Susan Owen

Registered User.
Local time
Today, 23:34
Joined
Jul 8, 2002
Messages
33
Copyrighted Database Design?

Hi Mark,

I've looked at what you've written and found it helpful (but I will be looking in much more detail and have more to say soon.)

One of my colleagues showed me his database design that is completely normalized and generic. You can use this for anything, whether a business manufacturing organization or products, or services, or non-profit. It's a sort of a "one-size fits all".

He told me that it was copyrighted. I wonder just exactly what that means, like in music, you can copyright the words, but it's not possible to copyright melodies and chords. So I can understand that an application can be copyrighted, but I don't think it's possible to copyright a database design. Who would ever know if someone else used your design? Besides, I rather think that he got this from somebody else to start with. Any information / ideas from you or anybody? I think it would be great to have this design available to use in production, and I've been searching the Internet but can't find it anywhere. Do you or anybody know of anything similar?
 

Users who are viewing this thread

Top Bottom