Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Database Design question

    Hello, I'm trying to design a database to apply the following rules:

    - There will be several Workgroups.
    - Each Workgroup belongs to a Section and to a Division
    - Division will be either 1 or 2, whatever the Section
    - Some sections may have subsections
    - The may be several people in each Workgroupd (at least 1) and each of those has a "job" (jobs can only be of 3 types)
    - Stuff that will be done with this data:
    --- display a list of all WGs (organized by Section, subsection and division)
    --- display information about each Workgroup (people, etc)
    --- display a list of all people
    --- display information about the people (which WGs do they work for)
    --- display a list of all sections

    Here's an example of the relations between Section/Subsection/Division/Workgroup (the list of WG could be shown this way). Note that the first two sections have not subsection.

    Code:
    Section 1
     Division 1
      Workgroup1
      Workgroup2
     Division 2
       Workgroup3
       Workgroup4
       Workgroup
    
    Section 2
     Division 1
       Workgroup5
     Division 2
        Workgroup6
    
    Section 3, SubSection 1
      Division 1
       Workgroup7
     Division 2
        Workgroup
    
    Section 4, SubSection 1
      Division 1
       Workgroup9
     Division 2
        Workgroup10
    
    Section 3, SubSection 2
      Division 1
       Workgroup8
     Division 2
        Workgroup11
    
    Section 4, SubSection 2
      Division 1
       Workgroup
     Division 2
        Workgroup
    Tables

    These are the tables and structure I've designed so far.

    workgroup (workgroup_id, title, section_id, sub_section_id, division, etc)

    workgroupPeople (workgroup_id, people_id, job)

    section (id, etc)

    subsction (id, section_id, name)

    people (people_id, etc)

    - "etc" means data like title, name, text

    - No table for division: there are only 2 possible, numeric values, and there's little chance that that will change in the near future.
    - No table for jobs: there are only 3 possible values (it's either P, T or PT) (stored as numeric values that are changed in the application code), also there's very little chance that that will change in the near future.
    - Section (4 possible values) and subsection (2 possible values) are bugging me. These are both numeric (although subs do have a title) and there's not a big probability that this will change, but I think the system should offer that possibility.


    What I want to know is people's opinion about this design (including the stuff that I've chosen not to use a table for). Any ideas and/or sugestions are welcome. If you need some more info on the data or what needs to be done with it, or if you need me to explain something in another way, please say so.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    I would create a single data table to hold all types of organizations (company, division, section, branch, workgroup, etc.) In that table I would have a foreign key from the org_type table (which just stores the list of org types like "company", "division", etc.)

    Table: ORG_TYPE
    ----------
    ORG_TYPE_ID (pk)
    ORG_TYPE_NAME
    ETC.

    Table: ORG
    ----------
    ORG_ID (pk)
    ORG_TYPE_ID (fk)
    ORG_NAME
    ETC.

    Table: PERSON
    ----------
    PERSON_ID (pk)
    PERSON_NAME
    ORG_ID (fk)
    ETC.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Angry

    Sounds like a homework assignment to me!
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I would create as generic a model as possible and then apply constraints.

    Section()
    Subsection()
    Division()
    Workgroup()
    WorkgroupPeople()
    Person()

    Many of the constraints mentioned can be built into the table definitions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •