Re: Re: Re: Re: Re: Re: ...
Quote:
Originally posted by OtarBoy
Sorry but what is the meaning of normalise?
One of the fundamental difference between a (basic) Spreadsheet and a Database in the way that they store their data is that Databases utilise relationships between tables to provide a more effective and efficient way of storing and accessing data.
Consider the following scenario. You have a spreadsheet of parts which are to be returned to their manufacturers. In this you have the columns "ITEM", "DESCRIPTION", "MANUFACTURER" and "DATE RETURNED". What is to stop the user from typing in "Wigdte" instead of "Widget"? The answer is nothing but the users unwillingness to enter garbage data, which any Database Developer knows it practically non-existent. You potentially could end up with a table full of parts, descriptions and manufacturers which are entered with deviations between each entry. Why is this a problem?
Consider this. You search for any and all items called "Widget A". If the user has entered "Widgt A" then that record will not be found. If the user entered "A Widget" or "Widget A" then the record will not be found. It gives rise to a lack of data integrity, reduces the search functions of you database, and in essence, fucks up your game.
In an Access 2k Database, using the example above, you would have a series of tables, linked by queries to provide the same data as the Spreadsheet above. Only the database (if designed correctly) would not have these problems that the Spreadsheet has. The reason for this is normalisation.
Normalisation is the process of identifying recurring areas of information within a table, and breaking that down into another table linked to the orignal table and identified by a Unique identifier (Primary Key).
In simple terms, your tables would look like this:
Items Table - Fields: ITEM_ID, ITEM_DESCRIPTION
Manufacturers Table - Fields: MANUFACTUER_ID, MANUFACTURER_NAME
Repairs Table - Fields: REPAIR_ID, ITEM_ID, MANUFACTURER_ID, DESCRIPTION.
Now, the tables would be linked via their Primary Keys in the following way.
ITEMS TABLE - ITEM_ID >--> REPAIRS TABLE - ITEM_ID
and
MANUFACTURERS TABLE - MANUFACTURER_ID >--> ITEMS TABLE MANUFACTURER_ID
Then in query, you'd reference all three tables together, and upon entering an ITEM_ID code into the REPAIRS Table, the database would look-up the details about this item in the ITEMS table.
As you can see, it isn't the easiest thing to explain on a forum. Take a look around some of the help within Access 2000, searching for keywords such as 'RELATIONSHIPS', 'PRIMARY KEYS', 'COMPOSITE KEYS' and 'RELATIONAL DATABASE'. Alternatively, buy a good book. "Running Access 2000" by John Viescas (Microsoft Press) is what I usually recommend for someone starting out in the world of Databases & Access.
Once you understand the fundamental processes of Databases then you can explore further into them. The biggest mistake you can make is to treat a Database like an alternate form of a Spreadsheet. Just because the Table View in Access resembles a Spreadsheet, doesn't mean that it is anything like it!