A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table Structure Help



 
 
Thread Tools Display Modes
  #1  
Old March 30th, 2010, 01:12 AM posted to microsoft.public.access.tablesdbdesign
Ron A.
external usenet poster
 
Posts: 50
Default Table Structure Help

I am needing some assistance in creating a solid table structure in creating
a database that stores vehicle permenant waiver items (items that will not be
repaired) and produce a report showing waivers for a specific vehicle or
organization. Specifics are as follows:

- 1 organization has a master Org Code
- an organization can have many sub Org Codes (multiple shops in 1 org)
- an organization can have many Vehicle Control Officers (usually 1 per sub
Org Code)
- Vehicles are assigned to master org code and sub org codes
- One vehicle can have many waivered items

I hope I have explained this well enough.

Thanks,
Ron A.
  #2  
Old March 30th, 2010, 03:05 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Table Structure Help

In the table of organisations, you will need fields like these:
- OrgID AutoNumber primary key
- ParentOrgID Number blank if this isn't a member of another org
- OrgName Text

Will there ever be a case where vehicles could be reassigned? If so, you
will need a table identifying what vehicle was assigned to what org and
when:
- VehicleID which vehicle was assigned
- OrgID who the vehicle was assigned to
- AssignDate when this vehicle was assigned to this org.
All 3 of those fields required, and a unique index on the combination of
VehicleID + AssignDate, so you can tell who it's currently assigned to.

Not sure how you do your waivered items, but it may be that the waivered
items depend on the vehicle type. a) If you had 15 instances of the same
kind of vehicle, would they all have the same waivered items? b) Or might
they be really individual (e.g. some have towbars or extras and you need to
handle those)?

If (a), you need a VehicleType table, linked one-to-many to the waivered
items. If (b), I suggest you still have a VehicleType table and a table of
default waivered items for that table, but the actual waivered items will
relate to the specific vehicle.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Ron A." wrote in message
...
I am needing some assistance in creating a solid table structure in
creating
a database that stores vehicle permenant waiver items (items that will not
be
repaired) and produce a report showing waivers for a specific vehicle or
organization. Specifics are as follows:

- 1 organization has a master Org Code
- an organization can have many sub Org Codes (multiple shops in 1 org)
- an organization can have many Vehicle Control Officers (usually 1 per
sub
Org Code)
- Vehicles are assigned to master org code and sub org codes
- One vehicle can have many waivered items

I hope I have explained this well enough.

Thanks,
Ron A.


  #3  
Old March 30th, 2010, 04:06 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Table Structure Help

Ron,

Maybe other people can figure out your data, but I don't belive that you
have provided enough detail. I think you need to provide some additional
backgroup detail.

Let's starts with a couple of questions.

1. Can there be multiple organizations or is there just one.
2. Can there be multiple Vehicle control officers per shop / sub Org Code.
3. Is a Vehicle Control Officer assign to a specific shop or do they work
out of any shop?
4. Why is a vehicle assigned both a master org code and a sub org code. In
this structure, you could assing the vehicle to a master org code that has
nothing to do with the sub org code. If you assign a sub Org Code to a shop,
and you assign a shop to an organization / master Org. When you assign a
vehicle to a sub Org Code, that automatically assigns that vehilcle to a
master org code. By having both fields, you are implying that you can assign
the vehicle to one master org code and to a totally different sub Org Code.
5. You don't tell us how you identify an organization, shop, vehicle
control officer, vehicle.
6. What is an Org Code, what is a Sub Ord Code? Is that what identifies an
organiztion / shop or is that just an attribute of the organization / shop?
7. Are the Vehicle Control Officer associated with a vehicle?

It seems that there are a lot of missing details!

tblOrganization
Key: Master Org Code
Data Org Name

tblShop
key: Sub Org Code
Data Master Org Code fk to tblOrganization
Shop Name

tblVehicleCtlOfficer
key: OfficerNo - automatically assigned system number.
Data: Sub Org Code

tblVehicle
key VehicleNo - automatically assigned system number.
Data Sub Org Code - This is the foreign key to tblShop.
This associates the vehicles with the
shop and
therefore the master organizations.
OfficerNo

tblWaiver
key WaiverNo - automatically assigned system number.
Data VehicleNo foreign key to the tblVehicle table.



Good luck

Dennis
  #4  
Old March 31st, 2010, 07:26 PM posted to microsoft.public.access.tablesdbdesign
Ron A.
external usenet poster
 
Posts: 50
Default Table Structure Help

Thanks Allen and Dennis. I know I didn't go into the greatest of detail, but
you have given me just the push I needed. Thanks again.
--
Aloha,
Ron A.


"Dennis" wrote:

Ron,

Maybe other people can figure out your data, but I don't belive that you
have provided enough detail. I think you need to provide some additional
backgroup detail.

Let's starts with a couple of questions.

1. Can there be multiple organizations or is there just one.
2. Can there be multiple Vehicle control officers per shop / sub Org Code.
3. Is a Vehicle Control Officer assign to a specific shop or do they work
out of any shop?
4. Why is a vehicle assigned both a master org code and a sub org code. In
this structure, you could assing the vehicle to a master org code that has
nothing to do with the sub org code. If you assign a sub Org Code to a shop,
and you assign a shop to an organization / master Org. When you assign a
vehicle to a sub Org Code, that automatically assigns that vehilcle to a
master org code. By having both fields, you are implying that you can assign
the vehicle to one master org code and to a totally different sub Org Code.
5. You don't tell us how you identify an organization, shop, vehicle
control officer, vehicle.
6. What is an Org Code, what is a Sub Ord Code? Is that what identifies an
organiztion / shop or is that just an attribute of the organization / shop?
7. Are the Vehicle Control Officer associated with a vehicle?

It seems that there are a lot of missing details!

tblOrganization
Key: Master Org Code
Data Org Name

tblShop
key: Sub Org Code
Data Master Org Code fk to tblOrganization
Shop Name

tblVehicleCtlOfficer
key: OfficerNo - automatically assigned system number.
Data: Sub Org Code

tblVehicle
key VehicleNo - automatically assigned system number.
Data Sub Org Code - This is the foreign key to tblShop.
This associates the vehicles with the
shop and
therefore the master organizations.
OfficerNo

tblWaiver
key WaiverNo - automatically assigned system number.
Data VehicleNo foreign key to the tblVehicle table.



Good luck

Dennis

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.