If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database creation
Creating a database getting the relationships right and Normalising
Question: I would like to build a database from the start, using the details below, what would be the best way to divide it into separate tables and assign primary/foreign keys? Help and advice Please Currently there is an excel spreadsheet (.xls) being used that has the following column headings and data types. SPN Number (Unique) RNUM Number (repeated values) MAKE Text (repeated values) SYSTEM text (repeated values) SYSTYPE text (repeated values) DESCRIPTION text (repeated values) SERIAL Number (unique) QUANTITY Number (repeated values) PRICE Currency (repeated values) REGION Text (repeated values) LOCATION Text (repeated values) CONFIRMED Date (repeated values) FIRSTNAME text (repeated values) LASTNAME text (repeated values) STOCKTAKE Date (repeated values) COMMENTS text (repeated values) However, before I got given the task of sorting this out to try to convert it to a set of relational database tables, lots of data was entered with no regard to keeping entries consistant - i.e dates like 03/10/05 and 03.10.05 being entered and mixed number and text entries - AB1234. Nice! Thus lots of errors and irrelevant dog poo in the cells. I just need advice on creating an ideal database structure so future entries can be made correctly and existing data can be migrated to it. Advice would be much appreciated. -- I failed to make the school chess team because my height! |
#2
|
|||
|
|||
Database creation
Just as a starting step, look at your fields very carefully, and determine
what natural groupings there may be. If this is a spreadsheet that is keeping track of items, that can be a good place to start. You might even start with just one big table, because everything seems to relate to the item. This is OK. But now look at the data in each field (what you had under column headings in excel). There will be some data which has a lot of repeats in it. These are good candidates for creating a new table which contains all of those data (without repeating them, of course). For instance, the Make could be in MakeTable, with fields of MakeID and MakeText. The MakeID would be the key. Then in your ItemTable, you would have a MakeID field which connects to the MakeID field in MakeTable, through the relationships chart. Keep separating out new tables, where it is logical, with the new tables being linked back to your original table. That is the quick and dirty way to do it. Establish all of your tables and relationships before putting any actual data into the tables. Since you already have an excel spreadsheet, you can look at the data there, and make judgments based on what you see. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Access can't open database | Scott B | General Discussion | 1 | June 28th, 2005 04:16 PM |
Converting 97 database to 2003 database and implications | John | Database Design | 1 | November 22nd, 2004 05:23 AM |
cannot change password | Richard | General Discussion | 13 | November 14th, 2004 10:00 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |