Re: Table Design

Thank you Dave, Pete, and StrayBullet for your suggestions. They are all
very helpful.

"StrayBullet via AccessMonster.com" wrote:

You could edit the Asset table to be just:

Assets Table
AssetID
AssetDescription

and add an Ownership table and OwnershipType table

Ownership Table
OwnershipID
AssetID
OwnershipTypeID
OwnerID

OwnershipType Table
OwnershipTypeID (autonumber)
OwnershipTypeDescription (Employee, Store, Division)

You could then populate the Ownership table with each Asset's Owner's
information - the combination of OwnershipTypeID and OwnerID would be unique.

This setup is easily filled using cascading comboboxes on your form. When
entering an Asset, you could simply choose the ownership type (Employee,
Store, Division) and then the next combobox would automatically filter to
allow selection of the owner from that group.


Weste wrote:
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

--
Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Table Design
    ... You could edit the Asset table to be just: ... and add an Ownership table and OwnershipType table ... OwnershipTypeDescription (Employee, Store, Division) ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Buckley finally sees the light
    ... letter someone who believes in ownership of all of the assets of the ... nation by the state with their benefits distributed equally among all ... I'm not a teacher, just a student. ...
    (soc.retirement)
  • Re: Moving company bank money around...
    ... owns the assets and cash. ... Surely ownership is transitive. ... The owners of a company indirectly ...
    (uk.business.accountancy)
  • Re: Moving company bank money around...
    ... goes hand in hand with control, and that it is self-evident that if you ... have control of the company assets, albeit indirect. ... clinging unnecessarily to a notion that ownership can only be direct. ...
    (uk.business.accountancy)
  • Re: The Guerrilla Guide to Interviewing (version 3.0)
    ... I am talking about a different kind of "ownership". ... management wants to sell the outcoming products of this project to some ... have it as a underling employee in a govt lab. ... After the consultant finishes the project, ...
    (sci.research.careers)