Hello everyone.
I have a working database that I'm having second thoughts about after having to maintain it for 2 years. It started as an Access database, then it was ported to phpMyAdmin (mariaDB, similar to MySQL). I hope someone can give me tips about my approach.
Its goal is to store electrical inspections. Here's the deal:
1. Each inspection must include 5 people of different types, and a single electrical classification defined by different fields in which, if there is a transformer, it must be defined by even more fields.
2. Each inspection must include at least one visit, but may be more.
3. Each visit must include 2 witness type people. And here's another catch, it requires 2 fields from any particular Id.
4. The 5 types of person for inspections are the following:
4.1. Client type, who is the one who asked the inspectors to carry out the work, could be the owner or could be a contractor that handles several companies.
4.2. Owner type, which can be a regular person or a company.
4.3. Signatory type, is the person who signs the contract.
4.4. Responsible type, is the person designated to assist the inspector during the visit.
4.5. Initiator type, is the person who is requesting the inspection in the papers to be signed (not necessarily the client).
5. All persons involved must identify themselves with one of 6 types of identification, unless they are foreigners, in which case they only need one type of identification. There must be a track of what Id was used for each inspection, so I can't just simply store the personId, I have to store what Id was used for that person, what phone, what email, etc. Remember those all can change overtime.
I know this is something best done using noSQL, but I'm curious to know what others might see fit. Attached are a picture of what I've been using and maintaining and a sample database if anyone wants to play with it. Once you see it, you might realize it requires a lot of alias tables and subqueries and since I can't use CTE, I end up with massive SQL statements. So what do you think?