Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    21

    Lightbulb Unanswered: Single Long or Multiple Short

    Hi,
    I wonder what thoughts are on the following scenario. I am currently writing an app for a large organisation. The members may be assigned to a number of designated groups. "Vocational Groups" consisting of people who work in specific job classifications. "Branch Sections" consisting of people who work in particular areas. (E.g. Health, v Local Government ). "Committees" people who are on specific committees, and their various roles on these committees. Etc.

    Currently each of these groupings had their own tables. Eg. "Vocational_Groups"
    vg_id
    vg_name
    ...
    Members
    me_id
    me_name
    ...
    Members are assigned to a group
    Vocational_Members
    vg_id
    me_id
    ...
    The same would take place for other groupings.
    Currently there are seven of these type of tables, having various numbers of records. Each are very quick to read into grids, etc. Joins are simple and straight forward.

    I am thinking of merging each of these tables into one long table. Lets call it "Group_Members". This would result in a table of some 7,500 records (note a member may belong to multiple groups) having the following structure.

    Group_Members
    gr_id
    me_id
    ANd a corresponding lookup table.

    As a consquence there would only be two tables as opposed to the fourteen ( one for each of the lookup group tables, and one for each of the related members).
    Can anyone see anyone see any benefits in this longer 2 table concept? Joins?

    Looking forward to hearing any ideas.
    Morke
    Last edited by morke; 10-18-11 at 07:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by morke View Post
    Group_Members
    gr_id
    me_id
    ANd a corresponding lookup table.
    corresponding lookup table? how do you plan to make this relationship?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    21
    I am trying to figure that out at the moment.
    Assuming a 'Groups' table
    gr_id
    gr_name
    ...
    and a 'Group_Members' table
    gm_gr_id
    gm_me_id (Members -> me_id)

    I can carry out a
    "SELECT gr_name FROM Group_Members M
    LEFT JOIN Groups G on M.gm_gr_id = G.gr_id where M.me_id = " & iMemId

    This will give me a list of names for each group to which the member belongs. However, positioning the returned data within a grid or within the correct labels on a form seems a bit messy.

    The other option is to run multiple queries against the table for each of the possible groups to which the member can belong
    Morke

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by morke View Post
    However, positioning the returned data within a grid or within the correct labels on a form seems a bit messy.
    my condolences

    but how is that a database issue?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    21
    As I said I was looking for views as to the best solution. Either way, I have imported all the other tables into one and am using individual queries to populate the picklists.
    A bit like 'One ring to bind them!"

Posting Permissions

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