I'm trying to develop an application for a local law firm.
At the present time, I need to be able to do the following:
1) Open a new case( a file) for a client - Each case will have a Unique File Number
2) When a file is opened, it needs to be allocated to a specific department( Family, Crime, etc...). This can be done by a suffix
E.g. 1010- CR - This belongs to the criminal department
3) There needs to be another suffix i.e. 1010-CR-A. The A indicates that this file is being handled by site A. This law firm has two branches.
The tricky bit starts from here:
Firstly,the firm wants to be able to tell if a particular client is an old or new client. For example, the firm wants to be able to tell if the client:
i) Was a previous client (from the name and DOB)
ii) What was the previous case
Also, if the client is a previous client; the firm wants to be able to create a new file number for the client from the previous information.
Secondly, once a file number and department has been allocated. The file will then need to be time recorded( to produce bill (each departments way of time recording is different)).
The objective of this database is the following:
1) To have a "Directory" of files
2) To be able to time record for each department.
3) To collect information from the database for administrative use(the financial information which will be collected whilst time recording).
I have created a few tables and relationships in MS Access, I have attached the relationship view from MS Access in a word document and the MS database. I have also tried to explain my reasoning for each table created and tried to explain the situation better. The database is also attached in the compressed file.
I need help structuring the database to do what is mentioned above. Once that is done, I know pretty much what to do for the time recording in terms of tables.
I need help with the tables and relationship if someone can please help me out. In my current structure, the data is not in normal form.
Please do ask any questions if you require any further information/clarification.