redesign

[slimjen]

All, using access 2003. I inherited a database that tracks invoices and tracking numbers. I want to migrate to SQL Server 2005. I am reviewing the database design for improvements. There are about fifteen tables. Most have two fields like id and a number code Ex. ID autonumber and AssetNum text field. They all have ID fields as autonumber. Most of the primary keys are fields with text data type. Probably because the data is alphanumeric. Also, the main data table consist of quite a few lookup fields. My first question: Are the ID autonumber fields a waist of space since it's not the primary key and are text fields as primary keys slower to when indexing? Second question refers to the lookup fields in the tables. I know the data is maintained in the main table but don't know how to redesign to avoid lookups in tables.
Can anyone help please with some best practices when it comes to database design.


Thanks

[orange]

Please post a jpg of your tables and relationships.

Primary keys are intended for the use of Access itself to maintain table relationships. The general consensus and technique is to use autonumber primary keys to ensure unique number for system usage.

Depending on what exactly you mean by

Second question refers to the lookup fields in the tables

lookups at the table level are to be avoided by most users. see http://access.mvps.org/access/lookupfields.htm

Traditional lookup tables are fine, it is the table level lookups that are the issue.

Here are some free video tutorials that describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html



Good luck.