Table Design

  • Thread starter Thread starter Weste
  • Start date Start date
W

Weste

I am building an asset tracking database and have a design question. An
asset can be owned by either an employee, store, or a division. I have a
table for employees, stores, and divisions. Each table with some of its
fields is below. My question is about the Assets table. I need to track the
owner of the asset in the Assets table. Should I have 3 separate fields in
the table to indicate owner – EmployeeID, StoreID, or DivisionID where only 1
of the 3 can be populated for an asset? This doesn’t seem to be the best
solution for a normalized design. However, I am stumped as how to improve it
since the owners are so different. Any help would be greatly appreciated.

Assets Table
AssetID
AssetDescription
EmployeeID
StoreID
DivisionID


Employees Table
EmployeeID
LastName
FirstName
DepartmentID
JobTitleID
TerminationDate

Stores Table
StoreID
StoreNumber
StoreName
DeparmentID

Divisions Table
DivisionID
DivisionName
DepartmentID

Departments Table
DepartmentID
DepartmentNumber
DepartmentName
 
Not sure I fully understand. So the OwnerID would link to the EmployeeID,
StoreID, and DivisionID based on the OwnerType? If I have a form that
displays the asset would the sql have to be dynamically created for each
asset displayed based on the asset type? For example if the asset is owned
by an employee the query would join the Assets table to the Employees table
to display the results? If the asset is owned by a store the query would
join the Assets table to the Stores table? If I wanted to report on all
assets would I do 3 union queries - 1 for each owner type? Thanks for your
help.
 
You wouldn't have to do any dynamic SQL. If you are linking th Asset Table
to the Employee table, you would filter the query on OwnerType to be the
value you use for Employee owned assests, for example.
If you wanted to link all four owner tables, a Union could work in some
cases, but if you have situation where you need fields you can't match up
with all 4 tables, you could link all 4 owner tables to the OwnerID field.
Then use a Calculated control in the query to show the owner's name (or
whatever other fields you want to use)
For example lets say each OwnerType is a numeric value
Employee = 1, Store = 2, Division = 3, Department = 4

OwnerDescr = Choose([OwnerType], [LastName] & " " & [FirstName],
[StoreName], [DivisionName],[DepartmentName])
 
Back
Top