Database Normalization and Table Structures

[Mohan Krishna]

Hi mmccarthy!

Very glad to You to keep such very good articles in the forum.

Thank You!

[MMcCarthy]

Hi mmccarthy!

Very glad to You to keep such very good articles in the forum.

Thank You!

I'm glad you like it.

[Alim]

It Is Really Helpful For Me

[MMcCarthy]

It Is Really Helpful For Me

I'm happy you found it useful.

[lostindb]

Hi ppl. i'm new to here and this article really helped me understand database design in easy words!!i'm also thankful for letting us know abt database design for mere mortals! i am starting a course on principles of distributed database systems. the book i'm referring to is principles of distributed database design by m. tamer ozsu..is there any book that you can recommend which can make it easier to understand like mere mortals?

[jpharps]

many thanks for the article, explains a few things that were confusing me!

[MMcCarthy]

You're very welcome.

[youmike]

Have just scanned all the comments very quickly. Someone asked why one normalises. The answer I'd offer is two-fold. First, and most important, it allows the database engine to do its job more quickly. secondly, if you don't normalize, your un-normalized database will come back and bite you when you are asked by a user to do something - usually a summary report - that wasn't originally thought of. Apart from Mary's excellent summary, I think that another excellent explanation is one by Paul Litwin called "Fundamenta ls of Relational Database Design", which is included in an old Access book published by Sybex.

If there is any interest, I could post a list of other reference sources that I've found useful. This would include the topic of a naming convention, incidentally, for this is an equally important topic, I think.

A concluding thought. Like most designers who use Access, I had to find out the importance of this topic the hard way. I only wish that Mary had written her article when I started (with Access 2 in 1995, I might add!)

[MMcCarthy]

If there is any interest, I could post a list of other reference sources that I've found useful. This would include the topic of a naming convention, incidentally, for this is an equally important topic, I think.

You are so right about a database biting you if you haven't normalised. I spend so much of my time fixing these issues. I'm often asked to go in and enhance an existing database with added features and reports. It can be difficult to explain sometimes why current structure of the database won't allow for these features.

Please feel free to post a list of reference sources in a comment here. (No commercial links though). I'm sure many would find it useful.

Regarding the naming conventions, you are right they are an important topic. You can either post them in a comment on here or if you feel like tackling it from an article point of view, feel free to draw up your own article in editors corner on the issue. You can then run the article by any of the Access mods (or myself) and the Chief Editor and get it moved to the HowTo section.

A concluding thought. Like most designers who use Access, I had to find out the importance of this topic the hard way. I only wish that Mary had written her article when I started (with Access 2 in 1995, I might add!)

It's very true. Most people who start using Access without any training in database design are working at a disadvantage. The key to the success of any database (regardless of language) is to design the correct structure first. It may seem a little difficult at first to get your head around the concepts but once you have used them for a while they become second nature.

Mary

[CyberSoftHari]

Awesome msquared, this article is good and helpful.
If there is a diagram for each NF, then it will be more friendliy.

[MMcCarthy]

Awesome msquared, this article is good and helpful.
If there is a diagram for each NF, then it will be more friendliy.

Just arranging something now, will be up shortly.

[MMcCarthy]

Just arranging something now, will be up shortly.

Haven't managed to come up with diagrams for 2NF and 3NF yet. Will think about it and come up with something later.

[CyberSoftHari]

Haven't managed to come up with diagrams for 2NF and 3NF yet. Will think about it and come up with something later.

Thank you for kind and fast response

[Talraith]

Good article on the different normal forms, very helpful to all.

One of the additional normal forms, sometimes known as fourth normal form (4NF), is the many-to-many relationship. As stated previously, this is not used very often, however, when you start designing various applications, you may find yourself in need of this.

4NF specifies how to create a many-to-many relationship between tables. Most typically this would be between two tables. The end result here is an additional table comprising of the primary keys of the tables in the many-to-many relationship. Additionally, all fields are part of the primary key.

In other words...

Say you have one table called "users" and another table called "groups." Each table has its own primary key. You want to have a feature which allows each user to join one or more groups.

You would then create a table with two columns: user_id and group_id. The primary key for this table would be both of those columns. This is important for data integrity as you don't want the same user to belong to a specific group more than once.

With this setup, you can easily get a list of all the groups a user belongs to. Just as easily, you could get a list of all the users in a specific group.

[RedFoxOne]

Noralization and table structures have always proven difficult for me as well.

JJ