Table Structure Help
in [Tables]
|
From: Ron A. on 29 Mar 2010 20:12 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.
From: Allen Browne on 29 Mar 2010 22:05 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." <RonA(a)discussions.microsoft.com> wrote in message news:F8F92EC3-771F-479C-98C8-67FF16D26C4A(a)microsoft.com... > 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.
From: Dennis on 29 Mar 2010 23:06 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
From: Ron A. on 31 Mar 2010 14:26 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
|
Pages: 1 Prev: Prevent duplicate groups of records in a table Next: Newbee Access DB structure help |