Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Simple design question

    I am a beginner designing a simple database.

    This is what I have so far:
    There is a table that has a list of companies. There is a table that has a list of users with their names, passwords, company they belong to. I have a login set up on the website which uses these tables.

    Here is what I need to add:
    Each user will have a set of data which consists of a number of claims. A claim will have a short description, its amount, and its date.

    This is a relatively small project. Each user will have at most 100-120 claims per year (this data is only relevant for a year at a time). There are usually between 1 and 50 employees per company, and about 20 companies.

    What is the best way to store the claims? I was thinking a table for each company filled with the claims for its corresponding users? I guess other options are just one table with all the claims, or a sperate tabel for each user. Any adivce would be appreciated.

    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Think about it this way:

    Companies are an entity

    Users are an entity

    Claims are an entity

    You don't need separate tables for an entity.

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    hmmm, so how should I set it up then? Are you suggesting all 1 table?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    nepatriotsxxxvi
    that's not really the question is it

    How do you think it should be set up?

    Certus have given you some ideas on the entities as he sees it.

    Analysing your problem, how do you see the entities

    How do you see the realtionship between entities

    How much complexity do you perceive in the real world, and how much of that complexity have you got to represent in your model?

  5. #5
    Join Date
    Dec 2005
    Posts
    3
    Alright, that pretty much solves my problem. Lets review what I've learned so far....

    ...

    ...

    nothing

    GREAT!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by nepatriotsxxxvi
    Alright, that pretty much solves my problem. Lets review what I've learned so far....
    So, when is the assignment due?

    -PatP

  7. #7
    Join Date
    Jan 2006
    Posts
    18
    i was about to blurt out a solution, then i thought of a complication i couldn't immediately think around:

    what would be a good way to allow users to switch companies, or be under multiple companies, while retaining which company a user was with for each claim? normalized of course. just adding company_id to each claim alongside the user_id seems.. wrong.

    how about an employment table that tracks which company a user was under for a given time period, such as
    Code:
    table employments:
    employment_id
    company_id
    user_id
    start_date
    end_date
    that way a claim would reference an employee_id, users could be in multiple companies or change companies, but for a given claim you'd still know the user and company responsible.

    is that on a better track?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by nepatriotsxxxvi
    Alright, that pretty much solves my problem. Lets review what I've learned so far....
    Hmmm... let's review what you've paid us to spoon-feed you answers amounts to so far...

    Quote Originally Posted by nepatriotsxxxvi
    nothing

    GREAT!
    Depth of your effort = Depth of our answers

    The sooner you grasp that the better.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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