Database design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings all. In my database every table is associated with a work order.
As such I have made the work order #, which is unique, be the primary key of
every table. I have a form titled 'jobinfo' on which general info is
entered, then I have put buttons to open forms based on the other tables, but
still sharing the same work order#. This design has been easy for me to set
up in that there is a one-to-one relationship beween all the tables, and it
is easy to make queries using the wizard. My concern is that my company has
approved a proposal to migrate our Access database to MS SQL Server 2005, and
I do not know how my set up will work. Can my data base tables be move to
SQL 2005 as is, and can I continue to use Access as a front end?
 
Yes, your design will work in SQL Server, because it has all of the data
storage and relational capabilities of Access and then some. And you can
still use Access as the fornt end by linking the SS tables to it using the
ODBC sources option.
 
The Order# should only be the Primary Key of the Work Order table itself.
It should be the Foreign Key of the other Tables,...not the Primary Key.

The Primary Key of a Table is in direct relationship the the "subject
matter" of the Table. If it is an Employee table then the Employee ID would
be the Primary Key,...the Order# could be a Foreign Key because an employee
would logically be "involved" with a particular Order#. The same principle
applies the Products Table, or Jobs Table, etc...which would all have IDs
inique to what the table "is about".

It is not likely that a 1-1 Relationship exist between all tables,...it is
amost inevitable that their will be some many-to-many's and and will
certainly be some one-to-many's. So I suspect that in itself is also a
design flaw that will probably bite you in the rear end with SQL Server. It
probably means that you have duplicated certain Data across the
Tables,...like maybe an employee's Name appearing in more than one Table for
example. Every piece of data should only ever appear once in only one
Table,...it is the way the Tables are Related and how they are Queried that
makes it all work together.
 
I see how I should not duplicate any data, but the work order # is the only
peice of data duplicated in every table. If I have work order# 8A01111, and
there are 10 different types of cable needed for that job, how could I do
what you suggest other then having a separate table for every type of cable
possible? If I did that for every aspect of every job I would literally need
hundreds of tables. I do not mind trying to build a database that large, but
is that too many tables?

Phillip Windell said:
The Order# should only be the Primary Key of the Work Order table itself.
It should be the Foreign Key of the other Tables,...not the Primary Key.

The Primary Key of a Table is in direct relationship the the "subject
matter" of the Table. If it is an Employee table then the Employee ID would
be the Primary Key,...the Order# could be a Foreign Key because an employee
would logically be "involved" with a particular Order#. The same principle
applies the Products Table, or Jobs Table, etc...which would all have IDs
inique to what the table "is about".

It is not likely that a 1-1 Relationship exist between all tables,...it is
amost inevitable that their will be some many-to-many's and and will
certainly be some one-to-many's. So I suspect that in itself is also a
design flaw that will probably bite you in the rear end with SQL Server. It
probably means that you have duplicated certain Data across the
Tables,...like maybe an employee's Name appearing in more than one Table for
example. Every piece of data should only ever appear once in only one
Table,...it is the way the Tables are Related and how they are Queried that
makes it all work together.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Greg Snidow said:
Greetings all. In my database every table is associated with a work order.
As such I have made the work order #, which is unique, be the primary key of
every table. I have a form titled 'jobinfo' on which general info is
entered, then I have put buttons to open forms based on the other tables, but
still sharing the same work order#. This design has been easy for me to set
up in that there is a one-to-one relationship beween all the tables, and it
is easy to make queries using the wizard. My concern is that my company has
approved a proposal to migrate our Access database to MS SQL Server 2005, and
I do not know how my set up will work. Can my data base tables be move to
SQL 2005 as is, and can I continue to use Access as a front end?
Click to expand...
Click to expand...
 
Greg Snidow said:
I see how I should not duplicate any data, but the work order # is the only
peice of data duplicated in every table.
Click to expand...

That is fine,...just don't make it the Primary Key. Make something else the
Primary Key of those other Tables (such as "Job").

You could have a single Table that lists all types of Cables in it, but you
may not have to go to that extreme. It would create a Many-to-Many
Relationship with a Linking Table that could become complex for little gain.

These two tables in this example are Joined by the fldOrderID which is the
Primary Key in one table and the Foreign Key on the other table.


tblOrders
------fldOrderID (Primary Key)
| fldOrderDetails
|
| tblJob
| fldJobID (Primary Key)
| fldJobLocation
| fldJobDetails
------fldOrderID (Foreign Key)
 
Back
Top