good design

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

I am trying to design a database and I am stumped on a
couple of things. There is a tremdous amount of
information we would like to collect, but the table would
be a mile long. For example, we have a primary field that
is employee name. We have about 40 groups this employee
can belong to. We also have a list of about 20 pieces of
software this employee could have on their work station.
Would it be bad design to have that many fields in one
table? How could I break it up so that when I enter the
information it would saved as information on each employee?
I would like to make two tables, but would that pose a
problem when writing to the database? How about upon
deleting employee from database?


Thanks,

Curt
 
Curtis said:
I am trying to design a database and I am stumped on a
couple of things. There is a tremdous amount of
information we would like to collect, but the table would
be a mile long. For example, we have a primary field that
is employee name. We have about 40 groups this employee
can belong to. We also have a list of about 20 pieces of
software this employee could have on their work station.
Would it be bad design to have that many fields in one
table? How could I break it up so that when I enter the
information it would saved as information on each employee?
I would like to make two tables, but would that pose a
problem when writing to the database? How about upon
deleting employee from database?


Thanks,

Curt
Click to expand...

Make 4 tables: 1/"Employees", 2/"Groups", 3/"Software" and 4/"Data". The
"Data" table should have an "EmployeeNo" field, a "GroupID" field, and a
"Software" field (and others).
Each of these fields in the "Data" table should be linked to the appropriate
key field in the other tables.
If you enforce referential integrity between (for example) the EmployeeNo in
the "Employees" table and the EmployeeNo in the Data table (in the
relationships window) and choose cascade update / cascade delete, then when
you delete an employee from the employees table. Access will delete all
related fields in the Data table.
Don't worry about the length of the table. If you want to gather the data
there's really no way around it. You can maximise it's efficiency by good
choice of field size in table design (stick your cursor in the field size
part of the property sheet in table design and hit F1).

HTH

Damo
 
Curtis said:
I am trying to design a database and I am stumped on a
couple of things. There is a tremdous amount of
information we would like to collect, but the table would
be a mile long. For example, we have a primary field that
is employee name. We have about 40 groups this employee
can belong to. We also have a list of about 20 pieces of
software this employee could have on their work station.
Would it be bad design to have that many fields in one
table? How could I break it up so that when I enter the
information it would saved as information on each employee?
I would like to make two tables, but would that pose a
problem when writing to the database? How about upon
deleting employee from database?
Click to expand...

You would have three tables...

Employees

Groups

Software

There would be a One-to-Many relationship between Employees and Groups as
well as between Employees and Software. Then you have two subforms on your
Employees form where you can enter as many Group records and/or as many
Software records as each employee needs.

This design doesn't care how many kinds of Groups there are currently or
how many new Group types might come along later. Likewise with Software
types. Compare that with what you would have to do if these were fields in
one big table. It would require a major redesign of your application every
time a new type of Group or Software came along. Querying against the one
big table would pose lots of problems as well.

This process of breaking entity types into separate related tables is
called "normalization". You should do some research on that topic before
proceeding.
 
I am trying to design a database and I am stumped on a
couple of things. There is a tremdous amount of
information we would like to collect, but the table would
be a mile long. For example, we have a primary field that
is employee name.
Click to expand...

That's unwise. I once worked with Dr. Lawrence David Wise, and his
colleague Dr. Lawrence David Wise. Names ARE NOT UNIQUE and they do
change, and therefore are unsuitable as primary keys.
We have about 40 groups this employee
can belong to. We also have a list of about 20 pieces of
software this employee could have on their work station.
Would it be bad design to have that many fields in one
table?
Click to expand...

Yes, very bad design indeed. This is called "committing spreadsheet
upon a database" and requires penance! said:
How could I break it up so that when I enter the
information it would saved as information on each employee?
I would like to make two tables, but would that pose a
problem when writing to the database? How about upon
deleting employee from database?
Click to expand...

You should have at least FIVE tables:

Employees
EmployeeID Primary Key
LastName
FirstName
<other bio information>

Groups
GroupName Primary Key
<or you may want a GroupID and a text GroupName>

Software
SoftwareID Primary Key
SoftwareTitle
Version
<other info, e.g. service packs applied>

GroupAssignments
EmployeeID << link to Employees
GroupName << link to Groups
<maybe info about this employee/this group, e.g. Role = "Team leader"
or a date range>

SoftwareAssigned
EmployeeID << link to Employees
SoftwareID << link to Software
<again, maybe other info>

You should strongly consider doing some reading about "Database
Normalization" - there's a good database design whitepaper at

http://support.microsoft.com/kbid=283698
 
-----Original Message-----


Make 4 tables: 1/"Employees", 2/"Groups", 3/"Software" and 4/"Data". The
"Data" table should have an "EmployeeNo" field, a "GroupID" field, and a
"Software" field (and others).
Each of these fields in the "Data" table should be linked to the appropriate
key field in the other tables.
If you enforce referential integrity between (for example) the EmployeeNo in
the "Employees" table and the EmployeeNo in the Data table (in the
relationships window) and choose cascade update / cascade delete, then when
you delete an employee from the employees table. Access will delete all
related fields in the Data table.
Don't worry about the length of the table. If you want to gather the data
there's really no way around it. You can maximise it's efficiency by good
choice of field size in table design (stick your cursor in the field size
part of the property sheet in table design and hit F1).

HTH

Damo


.
Click to expand...

Thanks guys! I followed Damo's advice on enforcing
reference integrity, but I want to use check boxes for my
software and group tables. Example, John Doe might have
the following software installed on his workstation;

Word
Excel
Access

When it comes time for my form I would like to use check
boxes to show this info. The same goes for the groups.
Is there a better way than using check boxes?

btw, I did follow you advice and change the primary key to
username and not name. Yes there could be two John Doe's,
but usernames are unique.

TIA

Curt
 
-----Original Message-----
linked
to the appropriate cascade
delete, then when to
gather the data

Thanks guys! I followed Damo's advice on enforcing
reference integrity, but I want to use check boxes for my
software and group tables. Example, John Doe might have
the following software installed on his workstation;

Word
Excel
Access

When it comes time for my form I would like to use check
boxes to show this info. The same goes for the groups.
Is there a better way than using check boxes?

btw, I did follow you advice and change the primary key to
username and not name. Yes there could be two John Doe's,
but usernames are unique.

TIA

Curt
.
Click to expand...

Hey Damo, how come I can't enter a new username in the
tblUuserName because it is telling me I have to have an
entry in the tblData to match it. Why is this?

Curt
 
Hey Damo, how come I can't enter a new username in the
tblUuserName because it is telling me I have to have an
entry in the tblData to match it. Why is this?
Click to expand...

You probably have the referential integrity backwards. It should be
set with a one to many relationship from tblUserName to tblData, not
vice versa.
 
Back
Top