(Msg. 1) Posted: Thu Jul 24, 2008 1:33 pm
Post subject: Table Design Add to elertz Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
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.
(Msg. 2) Posted: Thu Jul 24, 2008 2:01 pm
Post subject: RE: Table Design Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
> >
(Msg. 3) Posted: Thu Jul 24, 2008 2:19 pm
Post subject: RE: Table Design Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
> 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
> > >
"Weste" <Weste DeleteThis @discussions.microsoft.com> wrote in message
news:3167351E-A256-471A-B2CD-B5260A77DAAC@microsoft.com...
> 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
>> >
(Msg. 5) Posted: Fri Jul 25, 2008 2:19 am
Post subject: Re: Table Design Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You could edit the Asset table to be just:
Assets Table
AssetID
AssetDescription
and add an Ownership table and OwnershipType table
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
(Msg. 6) Posted: Fri Jul 25, 2008 6:28 am
Post subject: Re: Table Design Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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 >
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office Other