Table Normalization

jonobugs

Registered User.
Local time
Yesterday, 22:44
Joined
Apr 15, 2013
Messages
70
I'm really new to making databases, so I apologize if I say something really ... stupid.

I know that you're supposed to normalize your database, but I was wondering how far you should go.

For example: If I make a school database and in the student table I create a field called "Address", should that address be in a different table?

First rule is that no data should be repeated, right? So that means if that student has a sibling, they will also have the same address.

However, does that make it more difficult later on when doing data entry? I mean, you would need to enter the address before you can enter in the student's name.

I've been trying to figure out this type of problem for some time now and I keep going around in circles with it! Like I said before, I'm really new to all this.
 
First rule is that no data should be repeated, right? So that means if that student has a sibling, they will also have the same address.
Don't think of normalization as a way to eliminate "repeating" data. Normalization is about data integrity and accurately representing dependencies in your tables.

There are a few reasons why it probably makes sense to create an address table. One reason is that your school might want to record addresses for things other than students, e.g. staff, suppliers and other correspondents. A single address for all these avoids any ambiguity about where an address should get recorded and updated. It also consolidates all your logic for addresses in one place. Having one and only one place for each type of data is called Orthogonal Design or more loosely Don't Repeat Yourself.

Another reason to create a separate address table is that a student may need to be associated with more than one address.

It may help you to take a look at this article:
http://www.tdan.com/view-articles/5014/

It's not a good idea to allow the design of the user interface for data entry, or any other UI, to influence the database design. Design the database based on analysis of the business rules and make it an accurate representation of the information you want to record. Then implement the user interface you need on top of that.

I've been trying to figure out this type of problem for some time now and I keep going around in circles with it! Like I said before, I'm really new to all this.

Possibly it's a controversial opinion for this forum but I personally think that Access is not a good environment in which to learn about data management and database design matters. Access contains too much that is unconventional, proprietary and has nothing to do with databases - stuff that just confuses or misleads students.

It's important to learn about data management from sources that are not tied to any specific vendor's software. Then get some practical experience on one of the popular standard DBMSs before you move on to creating applications on a development platform like Java, .NET or Access.

Hope this helps.
 
@Button Moon - You are both right and wrong.

Access is not a good environment in which to learn about data management and database design matters.
For the study of data management and database design matters, you are absolutely correct.

It's important to learn about data management from sources that are not tied to any specific vendor's software.
Theoretically - yes. In practice no. A huge majority of the readers here are people clever enough to realize that something like Access exists, and can be used for their problem better than, say, Excel or PostIt's. So they go for it, because there is no budget or insight to go for a full blown development using the "approved" tools like Java, PHP, .Net, C++, MySQL etc ...

Access is, IMHO, a bl ....y miracle. It lets anybody with just a little common sense and perseverance do amazing stuff in no time and for a very small amount of money. It will be ugly. It will not be maintainable. It will not be user friendly. It will not be ideal. And yet - it will be there, which will make all happy, because it responds to the need better than anything else.
 
Would you store peoples names in a separate table. John, Bob, Ted, Mary Sally etc.

This is most likely going too far. But what if you were designing the Telephone directory for New York. Then it becomes a different situation.

In your Case I would not store Names in a separate table, nor would I store Street Names. However I would store Cities, States and Postal codes.

BTW. You can add new information with the aid of some simple code. Google On Not On List. But you don't have to worry about that just now.
 
@Button Moon - You are both right and wrong.

Theoretically - yes. In practice no. A huge majority of the readers here are people clever enough to realize that something like Access exists, and can be used for their problem better than, say, Excel or PostIt's. So they go for it, because there is no budget or insight to go for a full blown development using the "approved" tools like Java, PHP, .Net, C++, MySQL etc ...

Access is, IMHO, a bl ....y miracle. It lets anybody with just a little common sense and perseverance do amazing stuff in no time and for a very small amount of money. It will be ugly. It will not be maintainable. It will not be user friendly. It will not be ideal. And yet - it will be there, which will make all happy, because it responds to the need better than anything else.

Hi Spike,

I assume you are not suggesting that in practice it's more important for someone to be able to do "stuff" even if they lack the knowledge to do it right and as a result make dreadful mistakes and cause errors, expense and maintenance problems for their organization? In practice is it really better to be able to do things badly than to understand enough to do things well? I'm sure that's not what you meant to suggest.

My point was that it's important (if you want a project to have the best chance of success I mean) to learn some foundation knowledge about data management before committing yourself to building a database. You have said you agree with me that Access is not the best way to learn those lessons. Since foundation knowledge is by definition about principles rather than software I'm not sure why you want to disagree with my point that you can't/shouldn't expect to learn those principles solely or mainly by using some software. Trial and Error is the most expensive and least reliable form of education - especially in database design where mistakes can have very ugly and expensive consequences.
 
Possibly it's a controversial opinion for this forum but I personally think that Access is not a good environment in which to learn about data management and database design matters. Access contains too much that is unconventional, proprietary and has nothing to do with databases - stuff that just confuses or misleads students.

It's important to learn about data management from sources that are not tied to any specific vendor's software. Then get some practical experience on one of the popular standard DBMSs before you move on to creating applications on a development platform like Java, .NET or Access.

Hope this helps.[/QUOTE]

Thanks for your help. I think that I might have given people the wrong idea. I'm not a database student. I am really just trying to make a database with the tools that I have (which is Access). I don't want to make a really bad database though, so I'm trying to design it before I start making it.
 
Would you store peoples names in a separate table. John, Bob, Ted, Mary Sally etc.

This is most likely going too far. But what if you were designing the Telephone directory for New York. Then it becomes a different situation.

In your Case I would not store Names in a separate table, nor would I store Street Names. However I would store Cities, States and Postal codes.

BTW. You can add new information with the aid of some simple code. Google On Not On List. But you don't have to worry about that just now.

Thanks for the tips. I don't think I will put names in a separate table just yet as I only have about 100 people. I probably won't put cities in a separate table either because my students all live in the same city and it's highly unlikely that I will get any students from another city. I may separate areas though. I live in Japan and the address system works a bit differently (they use areas, not street names). There are a lot of repeating areas.
 
I think buttonmoon is wrong about access being a bad environment for learning about databases.

except for a very few (non-standard) features that are not mandatory by any means, access just implements SQL. it provides the wonderful query design pane insulating users from the extremely difficult business of actually writing SQL.

i think (one of) the real problems is that users try and implement really advanced stuff, without understanding the basics.

eg - "how can i prevent users copying my database?" type questions, or "how can i send an email with a pdf attachment", without really understanding how to construct the basic database in the first place.
 
I'm really new to making databases, so I apologize if I say something really ... stupid.

First rule is that no data should be repeated, right? So that means if that student has a sibling, they will also have the same address.

This statement is totally and completely incorrect. Not only that it is impossible.

What is correct and easy to do is to create separate tables to store Data that regularly repeats.

Examples could be a Town or City with its own postal code.
If you start typing Bed... eventually Bedrock will be displayed and its postal code. Now every time you use Bedrock it will be spelt the same way and the Postal code will be the same.

Other common things are Salutations, Countries, Year which are used a lot in WEB pages.

Colours is another example if you want to limit the choice.

Rule Number One in Access is to avoid people who would prefer to use something else. This is because they don't understand Access. The shortcoming is with those people not with Access.

I don't know why they attempt to answer questions here. They are uneducated fools.
 
For what it's worth, every software product/DBMS package is an implementation of the theory. These implementations are tempered with the reality of the expected user audience, sales, marketing, maintenance and development path among other things. That any one product is not ideal is hardly a surprise. Access has a broad following, you can learn some theory and you can have practical applications, and a pretty good user interface with Forms, Reports, ODBC support etc. MySQL has its following, and application as do others.
I agree that data management, data administration, data dictionary/repository, data modeling, IRM have underlying principles and concepts and database practitioners should at least have some basic knowledge of these. But to expect everyone on these forums to have a solid basis in the theory with good retention of all the details is not realistic. Especially when companies (M$oft) markets to the world that Access will solve everything ( or IBM/DB2)- maybe not in those words, but look at posters with 1 - 5 posts--they have Windows 7 or 8, Access 2010 or 2013 and usually 64 bit. They've bought the software, now where's my database! ( What do you mean there's theory and principles --- I don't have time. We need it now! What's a table? SQL?)

Some of us, and I would include ButtonMoon in the group, have a number of years in the field, and have learned a lot by getting involved, and learning best practices and experience; unlearning some practical workarounds. We didn't know it all from the start, don't know it all now and we're still learning. Let's face it Date, Chen and Zachman stuff was being written when many of us were working with the forerunners of today's RDBMS.
Suffice it to say some theory, some practical experience, more learning and more experience in whatever mix is where most people are. All theory and little to no experience would not be anyone's first choice. No theory, and a lot of bad practices wouldn't be anyone's first choice either. You can always learn if you're willing to listen, read, practice with an open mind. One size does not fit all, all the time.

Would anyone here hire a theoretical physicist to do the rough in electrical of your new house?
 
jdraw

If any of what you wrote was aimed at me then I must say I missed the point.

The topic is Normalisation, not would I employ Sheldon.
 
Rain,
None of what I wrote was aimed at anyone specifically.

The title is Table Normalization, but comments such as

Access is not a good environment in which to learn about data management and database design matters.
have to be put in context.
This is an Access forum so comments pro and con re Access are expected. People come here for advice, critique, recommendations, and the views of others. We all have different backgrounds. That doesn't make anyone wrong or right; just a different background.

My point is that all theory and no practical is not a goal. And all practical with no appreciation of theory is also not a goal.
Every software/DBMS is an implementation of some theory; complete with all the shortcomings the developer had to accommodate to make a viable product.
 
Last edited:
Rain,
None of what I wrote was aimed at anyone specifically.
My point is that all theory and no practical is not a goal. And all practical with no appreciation of theory is also not a goal.
Every software/DBMS is an implementation of some theory; complete with all the shortcomings the developer had to accommodate to make a viable product.

Wish you had of written that in the first place. That is far easier to understand.

Your previous reply was more like, hey guys look at me I can use big words.
 
Rule Number One in Access is to avoid people who would prefer to use something else. This is because they don't understand Access. The shortcoming is with those people not with Access.

I don't know why they attempt to answer questions here. They are uneducated fools.

Rainlover, People who aren't focussed on one software tool alone but feel able to recommend other approaches for certain scenarios? Those who having many years of experience in the field of database management choose to share their knowledge with others in these forums? To you those people are uneducated fools. I'll leave others to judge for themselves the credibility or foolishness of that claim. I don't engage in ad hominem attacks.

For jonobugs, my remarks were specifically about the suitability of Access as a platform for those learning about database management, given that you said you are new to making databases. The problem I had in mind was that Access weds some distracting UI elements a bit too closely to issues of database design and that often needlessly confuses those new to the subject. At least that's what I have noticed and at least one other here seems to agree. I had in mind your remark in your first post about the database design making things difficult for data entry - a consideration that really shouldn't alter sound database design practice. I hope the comments in my original reply might have helped a little because I tried to answer your original enquiry. Good luck anyway.
 
I like Access.
I think that the main drawback to Access is that it has way too much ability for the beginner.
MS should but out an Access lite for the beginner to learn on and to use for the small stuff they want.
Access has way too many rules, functions, methods and properties for a self taught first timer beginner.

In the beginning and sometimes still, I get overwhelmed just by trying to decide what command,function, method.......... I should be using.
Do I use ADO or DAO?

Just my thoughts.

Apologies to the OP for the highjack.
Dale
 
I like Access.
I think that the main drawback to Access is that it has way too much ability for the beginner.
MS should but out an Access lite for the beginner to learn on and to use for the small stuff they want.
Access has way too many rules, functions, methods and properties for a self taught first timer beginner.

In the beginning and sometimes still, I get overwhelmed just by trying to decide what command,function, method.......... I should be using.
Do I use ADO or DAO?

Just my thoughts.

Apologies to the OP for the highjack.
Dale

with respect, this statement is just daft.

there are two main aspects to a database. one is data-handling, and access implements this as basic SQL, I think, for the most part.

the second aspect is the user interface, and access provides a very rich developement tool

the alternative to having this combination is to provide just a text based console, where you have to type in each SQL statement.

i think the problem, as I said earlier in this thread, is just that some users try to develop very complex solutions without having sufficient basic understanding.

for instance - use ADO or DAO? out of interest, what do you need to use either for? 99% of stuff can be done directly with stored queries, without going near either of these. having said that if you need one of these, it ought to be a no-brainer, I think. DAO is well integrated into Access. I have never used ADO. I do not know if there is anything you exclusively need ADO for. I suspect not.
 

Users who are viewing this thread

Back
Top Bottom