1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Normalisation Question

Discussion in 'Business Applications' started by MRdNk, Sep 30, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi All,

    I'm in the process of designing and making a database, and need some advice on Normalisation.

    Looking at relationships between tables.

    First tbl - Stores,
    Second tbl - People

    Issue: Each store only has one DM, PM, PS, NSM, SM - these are specific jobs relating to the store; however each of these people categories are in the table people.
    (Talk about abbreviation overload)

    Options
    1. Should I separate each group into a separate table?
    2. Create a table between them? - (What should be included, just the primary from each table?)
    3. Or some other suggestion?

    Current setup is a spreadsheet, that just has the names of each person (abbreviation), however this isn't really good enough - as I want to expand some of this later on.

    NB. Each of the people (abbrev.) relates to a different field for each store in the store table.

    Option 2 seems like the most likely option, however I can't quite work out what I should put in this table.

    It's slightly odd, as it is a Many-to-Many; but also sort of not, because only one person will appear in each of the people groups fields, but there is technically more then 1 person per store.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    MRdNk, I have lost the hyperlink to a very good Site on Normalisation that Jim posted, but you might be able to find it if you search his posts for it.
    However I would say that you need to be a little clearer in your description for us to make a decision.
    Should you have a "Groups" table?
    Which is the Prime Focal Point, the Shops or the People. i.e. is the most important Table the Shops and the People are a Subtable and if so Can the same person be in more than one shop at the same time?
    If the Shop is the Prime Focus then you only need to have the ShopID in the People Table to identify which Shop they belong to. But if they can be in more than one shop then you would need a Many to Many Table.
    Similarly if the people only belong to one Group then you only need the GroupID in the People Table to identify their Group within their Shop.

    I hope I am making sense, as it is hard to "Imagine" your proposed setup.
     
  3. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    You're right, it isn't described very well.

    Let me explain a little better.

    Background:
    The database is a project management database for a store re-design project, and so we have a Project Manager, a Design Manager, a Store Manager etc.

    * There is only one of each group for a particular store - one project manager, one design manager, one store manager etc.
    * The store is the primary focus.

    Each store is unique, each Project Manager, Design etc, has more then one store.

    So I guess the solution is that I just need a GroupID, in tblPeople?

    Hope this is a little clearer at least.
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    From your new description I would say that the Managers are the Prime Focus, as they can have more than one shop. I think I would have a One to many table with Managers and Shops, as I would think there will be some overlap in who has what shops.
     
  5. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Oh okay, Prime Focus in that are the One to Many (stores) in the relationship.

    I guess I was thinking that because all the different tables and data will relate back to the store information that the tblStores would be the Prime Focus, but I understand what you mean now.

    Does my excel document set out the tables properly?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would do this
     

    Attached Files:

  7. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Excellent, thanks again T.O.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754761

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice