Normalizing Data

Now that you understand what a table is ... how do you design them? Just be simple ... but the simple way is not often easy to see! Tips to get you looking at your data from a different angle. (pdf file, install Acrobat Reader to read this tutorial.)

Sponsored Links:

Related Topics

First Normal Form (1NF) - Normalising Your Database
Database design theory includes design standards called normal forms. The process of making your data and tables match these standards is called normalizing data or data normalization. By normalizing your data, you eliminate redundant information and organize your table to make it easier to manage the data and make future changes to the table and database structure. This process removes the insertion, deletion, and modification anomalies you may see. In normalizing your data, you usually divide large tables into smaller, easier to maintain tables. You can then use the technique of adding foreign keys to enable connections between the tables.


Did I Normalize this correctly?
But I do have experience working with VB. I'm a highschool science teacher trying to help out the phys ed dept by creating a database to help keeptrack of the athletes, their teams, fee collection and jacket ordering.
After my first attempt resulted in failure (thanks Galaxiom for informing me I needed to normalize my data), I spent the next few hours reading a large number of posts trying to figure out what normalizing meant.

So. here's my attempt at normalizing the data. Any advise about my tables and relationships would be hugely appreciated as I now realise the importance of gettingthis step correct before spending hours making the forms.


Insurance Certificates Database
Things have slowed down in my department, so I'm back to working on my Contracts Administration database. When I left off, I was mired in designing tables for insurance certificates, unable to decide how far to go with normalizing. Under-normalizing results in large spreadsheet-like tables, which I am reliably informed are anathema to Access. On the other hand, I believe it is possible to over-normalize almost anything until the data is so scattered into many interrelated tables that performance is compromised, not to mention the unguessed sorrows to be had in writing VBA procedures under such conditions.

I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize.

I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements?


Making spreadsheets work in MS Access
I have noticed that some of the problems people experience in constructing a database are caused because they have not normalized their data.

Partly as an experiment to test some new software I found, I have constructed this presentation [LINK] which goes some way to explain the reason you should seriously consider normalizing your data.


Relational database concept example
And have been tasked with (what I hope to be) a fairly simple relational database problem. to get some feedback on whether or not this is a simple enough task for someone new to access, and 2. understand if this process seems correct.

I have data for 100 survey studies in excel that have already been broken down into 3 datasets. Study# uniquely identifies each survey, and is the same value across the 3 datasets. I washoping to be able to link all three data bases based on study#.

Below, I outline what is in each dataset, how I think I should be normalizing the data, and at the end describe how I think to combine the 3 data sets:

dataset 1:
each study can have multiple responses for country, audience and database, In excel it would look something like:


Convert Excel Flat File Into Relational Database
I have been tasked to convert an Excel Flat file into a Relational Database (sort off) to keep track of Shipments.

So I began grouping the headings first into sort of a related fields and the idea is to create a table base on the groupings and linked them together in Access (Relationship)

This is what I have come up so far Normalizing the headings.By the way it might be worth mentioning this. When I was looking at the Data on the Excel file. I have noticed that the Batch Number and Container columns have more Data on it. Meaning the Shipment on that day did not only contains more Batches of Item, but also has more Containers.

I am really hoping someone here can kindly help me putting this together.


"Normalizing" imported data
I have a situation where I'm importing substantial amounts of data (30,000 to 120,000 rows at a pop) from three external sources through a text file or an Excel spreadsheet into three different data tables. I've established lookup tables for those fields that can reasonably be normalized between and amongst the data tables.

The process I'm going through is:

1. Import the raw data into an "import" table that matches the structure of
the source data. Also included in the import table are columns for foreign keys of 'normalizable' fields, which are set to 0 when the source data is imported.

2. Append any new lookup data that may be present in the source file to the
lookup tables.

3. Run a series of update queries on the import table to update the foreign
key fields with the keys of the lookup data. Depending on the source data file, there are between 3 and 7 of these update queries.

4. Append new records into the data table using only the foreign key values
where applicable.

I'm discovering that the update queries in step 3 are taking a LONG time to run (several minutes each),

- are there other, better processes or data structures to use?
- is there a way of optimizing update queries?


Normalizing an Existing Database
A followup to the earlier article 'Modifying an Existing Program,' this article examines the process involved in converting an existing database into a normalized representation.


Highly Normalized Data and Wizards
I've been making my systems more and more normalized. My filter for something occurring more than once is infinity. So the question for normalizing is will it ever happen and can there ever be two.

I am now storing all companies (whether vendor, customer or any other) in one list.

In other words there is no longer a"tblVendors" or a"tblCustomers", there are now Vendor Agreements and Customer Agreements that each reference the mother table"tblCompanies".

Likewise all contacts in one list. And in both cases all of them have daughter tables for any data that can ever change,
So the table tblContacts can only have"FirstName","LastName","DateOfBirth", and if needed SS#, that's it. Everything else is in daughter tables.

There are a lot of problems with this.
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don't already exist and if they do, are they duplicates etc. etc. The list goes on and on...
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.

I can't tell you how much extra work this has caused and how many unforeseen data management issues; and users finding ways to do things you would have not dreamed possible.


Normalizing a Database
I am taking an online class and I decided to break the mold and not use one of the supplied databases as my project. The professor told me that if you want to do this make sure the database is normalized. I understand this concept but would like some experts to take a look at it. I have to have this down before I proceed on the rest of the project since everything builds off this.

A client can have many HOSTINGSUBCRIPTIONS
Each HOSTINGSUBSCRIPTION can only be linked to one HOSTINGPACKAGE
A client can have many INVOICEs
INVOICEs can have many LINEs

I believe HOSTINGSUBSCRIPTIONS and HOSTINGPACKAGE is correct, but INVOICE and LINES are not. It should be a M:N relationship, but I'm not sure how to properly show that.


Make table query against a normalizing select query.
If I import a table from Excel, normalize it in Access (which results in a select query) and then run a make table query against this new select query,have I negated the normalization I performed by turning the normalization select query back into a table?
The purpose of the make table query would be so that I could append the newly created table to an existing table.


Popup form
I began a project from very few fields in one table in a MS SQL database. It has now grown to several forms and several tables. I amstill in the process of "normalizing" the database.

What I have run into is the 255 field limit in a query. The query selects records from different tables to populate a form with tab controls and sub-forms.

After googling and reading I think a way to fix this for now is to use popup forms for specific form access when it is needed and not pull the information into the main form after the records is selected.

I had also tried researching to see if a separate query for each tab would work as well.

I am trying a popup form to test this theory. However, I am having an issue of the popup displaying the fields from the record I am currently on.


Sharing a many side table with more than one one-side tables
Note to long-time forums regulars: This is basically a rephrasing of my old question in my neverending quest for normalizing conflicting models & business requirements. I'm postingmainly to get some simulation on my thoughts among the way and see how I can improve and seeing what others may have done in solving similar problem to this.

After examining things a bit more closely, I realized I actually had a many side table which I needed to be shared between two one side tables that has nothing to do with each other. The many side table is primarily a log table, while the two one-side table has nothing in common and are not related at all.

Their relationship to the many-side table is primarily a mean forreference.

....

(This isn't really necessary as I use two separate forms for the respective updates/inserts, but I feel better having the extra constraint. and get a hearty headsmack in return)

Seems pretty straightforward and easy to setup, though in further analysis, this actually violates the normalization on two different fronts.


Popup form
and self-taught. I began a project from very few fields in one table in a MS SQL database. It has now grown to several forms and several tables. I am still in the process of "normalizing" the database.

What I have run into is the 255 field limit in a query. The query selects records from different tables to populate a form with tab controls and sub-forms.

After googling and reading I think a way to fix this for now is to use popup forms for specific form access when it is needed and not pull the information into the main form after the records is selected. I had also tried researching to see if a separate query for each tab would work as well.

I am trying a popup form to test this theory. However, I am having an issue of the popup displaying the fields from the record I am currently on.

Parent Form = frmProject
SubForm/PopForm = frmPOC_Popup
Parent/Child ID = ProjectID


Copy-paste every other record into bew table
I am kind of stuck here and really apreciate help. I upload an excel file into Acceess that I don't control the excel file .It is delivered to our firm via an e-mail and it is from a large usa bank. The problem is that the file is downloaded into two records for each account such as the below:
F1 F2 F3 F4 F5
1 DATA DATA DATA DATA DATA
2 DATA DATA DATA DATA DATA
3 DATA DATA DATA DATA DATA
4 DATA DATA DATA DATA DATA
The data consisted names, adreesses,phone numbres and so on , but they are break up into two records for each account. I am trying to copy-paste every second record ( record 2,4,6, and so on) into new table and create cross bar query to combine all datas for each account


Refresh or requery data
If you are sharing a database over a network, other users could be changing data while you are viewing the same data in a datasheet or form. Microsoft Office Access updates the data that you see at set intervals. However, you can immediately display the most current data by refreshing the records. You can also perform a more thorough update by requerying the data.
What do you want to do?
* Refresh data
* Set the refresh interval
* Manually requery data
* Use a macro to requery data
* Use code to requery data


Filling in data in query
I have a table with data for only one day a week (Thursdays). This data is used for that week until we get a new set the following Thursday.

The table with data is called VFF and has columns Date, SeriesName, Value. This is updated every Thursday.

I want to create a new table with all calendar dates where I populate the dates using the latest available data in that week.

i.e
Thu Nov 10 data from Nov 10
Fri Nov 11 data from Nov 10
Sat Nov 12 data from Nov 10
Sun Nov 13 data from Nov 10
Mon Nov 14 data from Nov 10
Tue Nov 15 data from Nov 10
Wed Nov 16 data from Nov 10
Thu Nov 17 data from Nov 17
Fri Nov 18 data from Nov 17

etc

I have tried to think of a few different solutions but I find they are not elegant nor very efficient.

If anyone has a good idea of how I should go about this I would be most grateful


Introduction to queries in Access 2007
When you want to review, add, change, or delete data from your database, consider using a query.
Using a query, you can answer very specific questions about your data that would be difficult to answer by looking at table data directly. You can use queries to filter your data, to perform calculations with your data, and to summarize your data. You can also use queries to automate many data management tasks and to review changes in your data before you commit to those changes.


How to securely delete data fields from tables in Access 2003
But I need to clear out a couple data fields which contain legally confidential data. If I simply delete these fields (variables), or overwritethe data with different data, is the original data actually gone? Do you need to compact the table to ensure it's deleted? Or, like with a computer, are there still traces of data in the .mdb file that someone could find and extract with special tools?


Tips: Working with data
Sharing data, Indexes, Data access pages, Queries, Creating and working with databases and objects, Archiving data, Viewing data, Importing and Linking Data or Objects.