|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 24th, 2008, 09:33 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Table Design
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 
|
|
#2
July 24th, 2008, 09:43 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design
How about:
Assets Table AssetID AssetDescription OwnerID OwnerType Then when you link to it from the different tables that can be owners, you filter it on the OwnerType for that table. -- Dave Hargis, Microsoft Access MVP "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
|
|
#3
July 24th, 2008, 10:01 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design
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. "Klatuu" wrote: How about: Assets Table AssetID AssetDescription OwnerID OwnerType Then when you link to it from the different tables that can be owners, you filter it on the OwnerType for that table. -- Dave Hargis, Microsoft Access MVP "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
|
|
#4
July 24th, 2008, 10:19 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design
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]) -- Dave Hargis, Microsoft Access MVP "Weste" wrote: 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. "Klatuu" wrote: How about: Assets Table AssetID AssetDescription OwnerID OwnerType Then when you link to it from the different tables that can be owners, you filter it on the OwnerType for that table. -- Dave Hargis, Microsoft Access MVP "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
|
|
#5
July 25th, 2008, 02:12 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design
Take a look at this example. http://www.allenbrowne.com/AppHuman.html
"Weste" wrote in message ... 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. "Klatuu" wrote: How about: Assets Table AssetID AssetDescription OwnerID OwnerType Then when you link to it from the different tables that can be owners, you filter it on the OwnerType for that table. -- Dave Hargis, Microsoft Access MVP "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
|
|
#6
July 25th, 2008, 03:19 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design
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
|
|
#7
July 25th, 2008, 02:28 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
