Best option for design of tables/fields(/relationships)
I'm new here; I've dabbled with FileMaker before but only on a rudimentary level. I have an understanding of the basics, of relationships etc.
I'm creating an HR database and am unsure of the best way to overall design the tables/fields. I can see a lot of issues with the way I *THINK* is best so I'm probably looking at it the wrong way.
Each employee start with a contract, that has various information (lets just say: name, address, salary, start date, stop date). This information can of course change over time and I need to track all changes. I also need the "employee" to always show the latest change.
Example: Hans Hansen, Waterstreet 1, earns 50.000, starts on 1/1/2014. He then gets a salary increase to 60.000 on 1/1/2015.
There are a medium amount of types of documents: Contract, Resignation, PayChange, VacationRequest, ManualChange etc.
There are quite a few information that need tracking: NameFirst, NameLast, NameNick, AddressStreet, AddressCity, Phone, Email, Salary, BankAccount, JobTitle, WorkingHours, and many more.
A typical scenario is we get an email saying: "Please change my bank account two months from now to xxx-xxxxxx". I'd like to register that right away, attach the email (for documentation) and be done with it.
Any idea of how to best design this?
I've thought about doing an EMPLOYEE table, a DOCUMENT table and a DOCUMENT_CONTENT table but that leaves me with obstacles:
- If the DOCUMENT_CONTENT only has ID_DOCUMENT, InformationType, Content then I have a problem in that some information is text, some is numbers etc.
- If I have tables DOCUMENT_NAME, DOCUMENT_ADDRESSSTREET and so on, I end up with a lot of tables.
- If the DOCUMENT_CONTENT has a column for each InformationType I end up with a lot of columns that are empty 99% of the time.
What am I not seeing?