Database creation

[MonkeyNut]

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.

 

[mnature]

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.