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