relational table design


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: relational table design

  1. #1
    Harolyn Guest

    relational table design


    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.

  2. #2
    Arthur Wood Guest

    Re: relational table design


    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.



  3. #3
    Harolyn Guest

    Re: relational table design


    "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.


  4. #4
    Arthur Wood Guest

    Re: relational table design


    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.
    >



  5. #5
    Harolyn Guest

    Re: relational table design


    "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?


  6. #6
    Arthur Wood Guest

    Re: relational table design


    >>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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center