relational table design

[Harolyn]

Hi everyone
I am sort of new to designing relational databases in Access. I have read
several books on the subject but am not having much luck with a project that
I am working on. I know that there is alot of knowledge out there and would
hope that I could get some direction.

As of right now I have 3 tables: MFGTable, EquipTable, and RenewalPartsTable.

MFGTable EquipTable RenewalPartsTable
MfgName EquipClass PartNumber (PK)
MfgID (PKP) KVRating PartName
EquipSubClass FigNumber

I know that the Mfg makes the equipment and the equipment is broken down
into the equipment class (ie breaker, transformer) which is further broken
down into subclass(ie oil circuit breaker, vacuum circuit breaker) which
all have a KV rating for the piece of equipment. I need to be able to tie
it all together to find the renewal parts for the equipment. It is basically
a database that will enable you to look up the information on the equipment
to have inventory control on rewenal parts and such.
There is an existing database with all of the things mentioned except for
the renewal parts table. It is large and includes other things that are not
needed at this time.

Can anyone help me with the setup of the tables that you see before you.
Any help will be apprecitated.

[Arthur Wood]

Several questions present themselves:

1) Does each entry in Equipment have only one manufacturer?
2) Each entry in EquipmentTable may have multiple SubClass entries?
3) What is there to tie RenewalParts Table to EquipmentTable?
(in other words, is there some kind of list of Equipment-PartNumber?)

If you can explain a little more about what you are trying to do then we
can help you with the design.

Arthur Wood

"Harolyn" <geauxbear@aol.com> wrote:
>
>Hi everyone
> I am sort of new to designing relational databases in Access. I have read
>several books on the subject but am not having much luck with a project
that
>I am working on. I know that there is alot of knowledge out there and would
>hope that I could get some direction.
>
>As of right now I have 3 tables: MFGTable, EquipTable, and RenewalPartsTable.
>
>MFGTable EquipTable RenewalPartsTable
>MfgName EquipClass PartNumber (PK)
>MfgID (PKP) KVRating PartName
> EquipSubClass FigNumber
>
>I know that the Mfg makes the equipment and the equipment is broken down
>into the equipment class (ie breaker, transformer) which is further broken
>down into subclass(ie oil circuit breaker, vacuum circuit breaker) which
>all have a KV rating for the piece of equipment. I need to be able to tie
>it all together to find the renewal parts for the equipment. It is basically
>a database that will enable you to look up the information on the equipment
>to have inventory control on rewenal parts and such.
>There is an existing database with all of the things mentioned except for
>the renewal parts table. It is large and includes other things that are
not
>needed at this time.
>
>Can anyone help me with the setup of the tables that you see before you.
>Any help will be apprecitated.

[Harolyn]

"Arthur Wood" <wooda@saic-trsc.com> wrote:
>
>Several questions present themselves:
>
>1) Does each entry in Equipment have only one manufacturer?
>2) Each entry in EquipmentTable may have multiple SubClass entries?
>3) What is there to tie RenewalParts Table to EquipmentTable?
>(in other words, is there some kind of list of Equipment-PartNumber?)
>
>If you can explain a little more about what you are trying to do then we
>can help you with the design.
>
>Arthur Wood
>
>"Harolyn" <geauxbear@aol.com> wrote:
>>
>>Hi everyone
>> I am sort of new to designing relational databases in Access. I have
read
>>several books on the subject but am not having much luck with a project
>that
>>I am working on. I know that there is alot of knowledge out there and would
>>hope that I could get some direction.
>>
>>As of right now I have 3 tables: MFGTable, EquipTable, and RenewalPartsTable.
>>
>>MFGTable EquipTable RenewalPartsTable
>>MfgName EquipClass PartNumber (PK)
>>MfgID (PKP) KVRating PartName
>> EquipSubClass FigNumber
>>
>>I know that the Mfg makes the equipment and the equipment is broken down
>>into the equipment class (ie breaker, transformer) which is further broken
>>down into subclass(ie oil circuit breaker, vacuum circuit breaker) which
>>all have a KV rating for the piece of equipment. I need to be able to tie
>>it all together to find the renewal parts for the equipment. It is basically
>>a database that will enable you to look up the information on the equipment
>>to have inventory control on rewenal parts and such.
>>There is an existing database with all of the things mentioned except for
>>the renewal parts table. It is large and includes other things that are
>not
>>needed at this time.
>>
>>Can anyone help me with the setup of the tables that you see before you.
>>Any help will be apprecitated.
>
Thanks Arthur for your speedy response!!!
The answer to question 1: is that equipment has several different manufacturers.
In other words a breaker can be made by several different manufacturers along
with the different subclasses. I was thinking that there maybe another table
for the subclasses.
The answer to question 2: may have been answered with question 1 but a breaker
can be different types in the subclass. Say GE (MFG) can make a breaker (EquipClass)
which maybe of type vacuum blast, or oil blast (SubClass).
The answer to question 3: I had not thought about how to tie the tables together.


The task at hand is to be able to go to a screen that allows you to select
a piece of equipment which would be selected by KV rating, type, and subtype.
Along with the correct manufacturer then be able to pick up a listing of
the renewal parts and the ones in stock, and the location of the stock for
that particular type of equipment.

[Arthur Wood]

You will nedd several additional tables:

Since Each Equip Class can have several Mfgs and Each Mfg can have several
Equip Classes (this is called a many-to-many relation, in database language,
and these are No Nos), you will need a Table (EquipMfg) which links MfgTable
and EquipTable

EquipMfgTable
EquipClass
MfgID


Also, since each Equip Class can have several subclasses, and presumably
the same subclass could possible appear in several Equip Classes you would
then need a table for SubClass and then a table EquipSubClass which would
resolve that potential many-to-many relation.

SubClassTable EquipSubClassTable
SubClassID EquipClass
SubClassDesc SUbClassID


you will have to think about ho, in the real world, RenewalParts are tied
to Equipment. For instance, does is a piece of Equipment (real world) composed
of several parts? then you would need to represent that composition in the
database:

EquipmentPartsTable
EquipmentClass
PartNumber

for example.

Hope this gives you a start.
Arthur Wood

"Harolyn" <geauxbear@aol.com> wrote:
>
>"Arthur Wood" <wooda@saic-trsc.com> wrote:
>>
>>Several questions present themselves:
>>
>>1) Does each entry in Equipment have only one manufacturer?
>>2) Each entry in EquipmentTable may have multiple SubClass entries?
>>3) What is there to tie RenewalParts Table to EquipmentTable?
>>(in other words, is there some kind of list of Equipment-PartNumber?)
>>
>>If you can explain a little more about what you are trying to do then we
>>can help you with the design.
>>
>>Arthur Wood
>>
>>"Harolyn" <geauxbear@aol.com> wrote:
>>>
>>>Hi everyone
>>> I am sort of new to designing relational databases in Access. I have
>read
>>>several books on the subject but am not having much luck with a project
>>that
>>>I am working on. I know that there is alot of knowledge out there and
would
>>>hope that I could get some direction.
>>>
>>>As of right now I have 3 tables: MFGTable, EquipTable, and RenewalPartsTable.
>>>
>>>MFGTable EquipTable RenewalPartsTable
>>>MfgName EquipClass PartNumber (PK)
>>>MfgID (PKP) KVRating PartName
>>> EquipSubClass FigNumber
>>>
>>>I know that the Mfg makes the equipment and the equipment is broken down
>>>into the equipment class (ie breaker, transformer) which is further broken
>>>down into subclass(ie oil circuit breaker, vacuum circuit breaker) which
>>>all have a KV rating for the piece of equipment. I need to be able to
tie
>>>it all together to find the renewal parts for the equipment. It is basically
>>>a database that will enable you to look up the information on the equipment
>>>to have inventory control on rewenal parts and such.
>>>There is an existing database with all of the things mentioned except
for
>>>the renewal parts table. It is large and includes other things that are
>>not
>>>needed at this time.
>>>
>>>Can anyone help me with the setup of the tables that you see before you.
>>>Any help will be apprecitated.
>>
>Thanks Arthur for your speedy response!!!
>The answer to question 1: is that equipment has several different manufacturers.
>In other words a breaker can be made by several different manufacturers
along
>with the different subclasses. I was thinking that there maybe another table
>for the subclasses.
>The answer to question 2: may have been answered with question 1 but a breaker
>can be different types in the subclass. Say GE (MFG) can make a breaker
(EquipClass)
>which maybe of type vacuum blast, or oil blast (SubClass).
>The answer to question 3: I had not thought about how to tie the tables
together.
>
>
>The task at hand is to be able to go to a screen that allows you to select
>a piece of equipment which would be selected by KV rating, type, and subtype.
>Along with the correct manufacturer then be able to pick up a listing of
>the renewal parts and the ones in stock, and the location of the stock for
>that particular type of equipment.
>

[Harolyn]

"Arthur Wood" <wooda@saic-trsc.com> wrote:
>
>You will nedd several additional tables:
>
>Since Each Equip Class can have several Mfgs and Each Mfg can have several
>Equip Classes (this is called a many-to-many relation, in database language,
>and these are No Nos), you will need a Table (EquipMfg) which links MfgTable
>and EquipTable
>
> EquipMfgTable
> EquipClass
> MfgID
>
>
>Also, since each Equip Class can have several subclasses, and presumably
>the same subclass could possible appear in several Equip Classes you would
>then need a table for SubClass and then a table EquipSubClass which would
>resolve that potential many-to-many relation.
>
> SubClassTable EquipSubClassTable
> SubClassID EquipClass
> SubClassDesc SUbClassID
>
>
>you will have to think about ho, in the real world, RenewalParts are tied
>to Equipment. For instance, does is a piece of Equipment (real world) composed
>of several parts? then you would need to represent that composition in
the
>database:
>
> EquipmentPartsTable
> EquipmentClass
> PartNumber
>
>for example.
>
>Hope this gives you a start.
>Arthur Wood
>
>"Harolyn" <geauxbear@aol.com> wrote:
>>
>>"Arthur Wood" <wooda@saic-trsc.com> wrote:
>>>
>>>Several questions present themselves:
>>>
>>>1) Does each entry in Equipment have only one manufacturer?
>>>2) Each entry in EquipmentTable may have multiple SubClass entries?
>>>3) What is there to tie RenewalParts Table to EquipmentTable?
>>>(in other words, is there some kind of list of Equipment-PartNumber?)
>>>
>>>If you can explain a little more about what you are trying to do then
we
>>>can help you with the design.
>>>
>>>Arthur Wood
>>>
>>>"Harolyn" <geauxbear@aol.com> wrote:
>>>>
>>>>Hi everyone
>>>> I am sort of new to designing relational databases in Access. I have
>>read
>>>>several books on the subject but am not having much luck with a project
>>>that
>>>>I am working on. I know that there is alot of knowledge out there and
>would
>>>>hope that I could get some direction.
>>>>
>>>>As of right now I have 3 tables: MFGTable, EquipTable, and RenewalPartsTable.
>>>>
>>>>MFGTable EquipTable RenewalPartsTable
>>>>MfgName EquipClass PartNumber (PK)
>>>>MfgID (PKP) KVRating PartName
>>>> EquipSubClass FigNumber
>>>>
>>>>I know that the Mfg makes the equipment and the equipment is broken down
>>>>into the equipment class (ie breaker, transformer) which is further broken
>>>>down into subclass(ie oil circuit breaker, vacuum circuit breaker) which
>>>>all have a KV rating for the piece of equipment. I need to be able to
>tie
>>>>it all together to find the renewal parts for the equipment. It is basically
>>>>a database that will enable you to look up the information on the equipment
>>>>to have inventory control on rewenal parts and such.
>>>>There is an existing database with all of the things mentioned except
>for
>>>>the renewal parts table. It is large and includes other things that are
>>>not
>>>>needed at this time.
>>>>
>>>>Can anyone help me with the setup of the tables that you see before you.
>>>>Any help will be apprecitated.
>>>
>>Thanks Arthur for your speedy response!!!
>>The answer to question 1: is that equipment has several different manufacturers.
>>In other words a breaker can be made by several different manufacturers
>along
>>with the different subclasses. I was thinking that there maybe another
table
>>for the subclasses.
>>The answer to question 2: may have been answered with question 1 but a
breaker
>>can be different types in the subclass. Say GE (MFG) can make a breaker
>(EquipClass)
>>which maybe of type vacuum blast, or oil blast (SubClass).
>>The answer to question 3: I had not thought about how to tie the tables
>together.
>>
>>
>>The task at hand is to be able to go to a screen that allows you to select
>>a piece of equipment which would be selected by KV rating, type, and subtype.
>>Along with the correct manufacturer then be able to pick up a listing of
>>the renewal parts and the ones in stock, and the location of the stock
for
>>that particular type of equipment.
>>
>Thanks again Arthur. If I understand you correctly I am on the right track
except that I didn't have all the appropriate tables. I was wondering if
the equipment table is correct with EquipClass being selected as the primary
key?

[Arthur Wood]

>>Thanks again Arthur. If I understand you correctly I am on the right track
>except that I didn't have all the appropriate tables. I was wondering if
>the equipment table is correct with EquipClass being selected as the primary
>key?
>
That depends on what the Table is to represent. I would think that you
probabaly have lots of Pieces of Equipment of a particualr class, in which
case you probably want to have your EquipmentTable look something like:

EquipmentTable
--------------------
EquipmentID (PK)
EquipmentClass
etc


and then use EquipmentID in EquipMfg Table, EquipSubClass etc.

What you are really looking at is an exercise in Data Modelling. You want
your data model to represent the real world entities (what infor do you need
to store about a Manufacturer, Piece of Equipment, ReplaceMentPart etc) with
only as much detail as is really necessary for the application. This is
sometimes called data abstraction.

Arthur Wood