Database structure with multiple people in the same table.

[Edgar]

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?

[CJ_London]

I think your design is wrong - looks like the starting point was excel thinking

as a start, repeating data ( your people, phone, email and no doubt datakey) should be represented in another table with a one to many relationship.

not sure why you are storing phone, email in separate tables, would think they are elements of a person.

[Micron]

Design doesn't look right - needs consolidation. I'd identify more of what the entities are and those should be in their own tables.
F'rinstance, consider the people involved. The only thing that seems to separate them is their role, otherwise they're all just people - but they need a "label" that defines the table name. Let's say that's tblContacts. Then tblContacts has among other fields, ContactTypeID, which is the fk that relates to the pk of tblContactTypes.
tblContacts record for me shows 5 in the type field (5 being the ContactTypeIDpk from tblContacts) because that's my type (it doesn't show "Initiator" text). The rest of the fields show FName, LName, Cell, Phone, Fax, etc.

If you need the ability to have multiples of any field then you start looking at junction tables, where (e.g.) you break out phone number types into its own table. If not, you end up with multiple records that duplicates everything about me, such as FName, LName - not good. In summary, you don't have repeated fields in tables for any attributes like what you have: owner email; sign email, init email etc. I really think that the current design is a large part of your current difficulties.

A review of normalization might be beneficial before doing anything else:
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Edgar]

Thanks, CJ_London

not sure why you are storing phone, email in separate tables, would think they are elements of a person.

As I mentioned in my 5th point, phone, email, and even some IDs can change over time. If I change something today, there will be a discrepancy in previous records, if I were to reprint an inspection report for a client or authority.

Edit
However, I do believe a many to many approach wouldn't hurt. Hmmm, my memory fails me but I think I didn't choose that path because I wanted to restrict it to only three and a many to many would allow any number.

[Micron]

some IDs can change over time

That should not matter if the proper use of PK's are used (as far as I can tell from your posts). I guess that might depend on what "ID's" means. The rest is of no consequence, methinks. Why would it matter if my email address (or similar) changed? Surely it's not a field that is stored in inspection records?

[Edgar]

Micron, mind elaborating on what you said in post #3? Basically having many to many relationships that look like this perhaps? I'm preparing a screenshot for what I believe you guys are suggesting me.
Is it perhaps this?


Or this?


A combination of both? or something else?

[ArviLaanemets]

Never keep same type of information in several tables, unless there is a real need for this (like when e.g. for inspectors you need to store some additional essential information). So the first thing I'd advice, keep the number of persons tables as low as possible. In same table, you can have any information about person, which will never change, or you aren't interested in keeping history of it. I.e. when you have a single address for any person, and you need this only to contact this person, you can have the address as field(s) in persons table. In case some of persons can have e.g. several addresses, or e.g. you need to know what was the address of this person when he/she was visitor in inspection a year ago, then you need a separate table where you register addresses of all persons, and probably with a field indicating, is this address in use currently or not).

Another data which need to be consolidated are data keys. You need a table, where you can register all data keys for given person along with key type (When you have separate contact info table(s), like person's mail addresses, phone numbers, etc., then a table like tbPersonDataKeys: PersonDataKeyID, PersonID, DataKeyType, ContactID, where DataKeyType determines the contact table the ContactID belongs to. When a person can always have a single contact of given type, and this info is saved to persons table, then probably you can drop ContactID)

Probably all inspectors never can be visitors. When this is the case, and you don't have a separate table for them, you can have a field in persons table, which determines, is this person inspector, or visitor. This allows to limit the list of available persons when you link persons to specific inspection.

I don't see a table, where objects of inspections are registered. It's logical to assume, you can have more inspections on those objects than one. When so, you need a separate table for objects, and probably info like ElectricThingType, or HasTransformer, etc., belong to this table (or to junction tables linked with objects table, in case when e.g. more than one ElectricThingTypes belong to object).

Then you need a table, where specific inspections are registered. When the inspection is done in one go, or there is no need to register, on which day any step of inspection was done, then you can do with single inspections table. When not, then you need a general inspections table, like tbInspections:InspectionID, ObectID, StartDate, FinishDate, InspectionResult, ... , and you also need e.g. an InspectionSessions: InspectionSessionID, InspectionID, SessionDate, ..., SessionResult table.

Then you need a table, where all persons involved in this inspection, or in given session of this inspection are registered, like tblInspectionSessionsPersons: InspectionSessionPersonID, InspectionSessionID, PersonRole, PersonID, PersonDataKeyID, ...
PersonRole determines, is the person in this inspection/session inspector, or client, or owner, or whatever.

Etc.

[Micron]

Couldn't have said it better myself. To even attempt to do so would, IMO, require a much better understanding of the process than what I currently have.

[Edgar]

Okay, my bad, I was not clear enough. Please correct me if I'm wrong: Are you thinking that I have multiple people tables, multiple phones tables, multiple email tables and multiple datakeys tables? Because that's not the case, there's only one of each. In the screenshots, you can see multiple tables BECAUSE I WANTED THE "LINKING LINE" TO APPEAR . I know, my bad. But in reality, there's only one table in the database. I think I made the mistake of adding all those "extra" tables in the last screenshot when I only really needed one of each to show those pretty lines. Anyway since both of you, Micron and Arvi, seem to agree in post #7, I will multi quote it now. I don't actually know how to use the multiquote tool, so please bare with me.

Never keep same type of information in several tables, unless there is a real need for this (like when e.g. for inspectors you need to store some additional essential information). So the first thing I'd advice, keep the number of persons tables as low as possible. In same table, you can have any information about person, which will never change, or you aren't interested in keeping history of it. I.e. when you have a single address for any person, and you need this only to contact this person, you can have the address as field(s) in persons table. In case some of persons can have e.g. several addresses, or e.g. you need to know what was the address of this person when he/she was visitor in inspection a year ago, then you need a separate table where you register addresses of all persons, and probably with a field indicating, is this address in use currently or not).

Yes. There is only one table for people, phones, emails and datakeys. The only thing that gets stored with each person, along with their name, is whether they are a company and whether they are foreigners. The rest of their data gets stored in a phones table, an emails table and a datakeys table, as all those are likely to change at some point. As for the address, by the way, it is stored with the inspection because there can only be one subject of inspection, and the address helps locate it. The witnesses table also requires an address, but its purpose is different, it is just necessary to include it as a datakey from the witness' Identification card.

Another data which need to be consolidated are data keys. You need a table, where you can register all data keys for given person along with key type

That's exactly what my datakeys table does. Its fields are: DataID, IdentKey, IdentTypeID, PersonID.

(When you have separate contact info table(s), like person's mail addresses, phone numbers, etc., then a table like tbPersonDataKeys: PersonDataKeyID, PersonID, DataKeyType, ContactID, where DataKeyType determines the contact table the ContactID belongs to. When a person can always have a single contact of given type, and this info is saved to persons table, then probably you can drop ContactID)

I definitely can't use ContactID in tbPersonDataKeys: PersonDataKeyID, PersonID, DataKeyType, ContactID. I have a table for phones: PhoneID, PersonID, Phone; and a table for emails: EmailID, PersonID, Email. The particular phone used for the particular inspection are stored in the inspections table.

Probably all inspectors never can be visitors. When this is the case, and you don't have a separate table for them, you can have a field in persons table, which determines, is this person inspector, or visitor. This allows to limit the list of available persons when you link persons to specific inspection.

Well, there is a table for inspectors, which I didn't think was necessary for this example. My people table is more like a "people involved in inspections". If one inspector were to be subject of inspection, I add it to the people table. Inspectors and people have different properties, just like clients have different properties, which is why I keep them separate.

I don't see a table, where objects of inspections are registered. It's logical to assume, you can have more inspections on those objects than one. When so, you need a separate table for objects, and probably info like ElectricThingType, or HasTransformer, etc., belong to this table (or to junction tables linked with objects table, in case when e.g. more than one ElectricThingTypes belong to object).

There are no objects of inspection, as in "this light bulb will be inspected, this electric outlet will be inspected, etc", the address is inspected as a whole.

Then you need a table, where specific inspections are registered. When the inspection is done in one go, or there is no need to register, on which day any step of inspection was done, then you can do with single inspections table. When not, then you need a general inspections table, like tbInspections:InspectionID, ObectID, StartDate, FinishDate, InspectionResult, ... , and you also need e.g. an InspectionSessions: InspectionSessionID, InspectionID, SessionDate, ..., SessionResult table.

My InspectionSessions table is my visits table. One inspection can have at least one visit with a result, but there can be multiple visits until the inspection's output is favorable for the client.

Then you need a table, where all persons involved in this inspection, or in given session of this inspection are registered, like tblInspectionSessionsPersons: InspectionSessionPersonID, InspectionSessionID, PersonRole, PersonID, PersonDataKeyID, ...
PersonRole determines, is the person in this inspection/session inspector, or client, or owner, or whatever.

Etc.

I agree with this. It should have been like this from the beginning. Each session (for your example) or visit (for my case) should have the people there. But the client said it was overkill to specify all the people in every visit, because the owner, the signatory, the responsible and the initiators were unlikely to change. Only the witnesses were likely to change.

Has any of my answers helped clarify how it works? Thank you for your time. So far I've only said no to your suggestions, I'm sorry about that. It's just that I think I confused you with the screenshots making you believe I have my data fractionated in a not normalized way. There is normalization going on. Perhaps not in the most strict way, like maybe Clients, Inspectors and Involved people should all have a PersonID foreign key, but that was intentional.

[CJ_London]

might be an idea to show a screenshot of the relationships - will be much clearer than trying to describe everything

[orange]

Edgar,
What is the current status? It is now phpMyAdmin based right?
Are you planning to move back to Access? Do you have online/internet based clients/users?

In a sentence or 2, what is the business?
I see visits and inspections and people (serving roles). I'm not sure how these relate in business terms.

[Edgar]

Current status: supporting both platforms, as both have clients/users.
The goal: migrating all users to the web version.

The business model: If someone with a non-residential property wants to have electricity, they must have approval from an inspector. The inspector visits the property and determines through a series of measurements if the electrical installation is well done. If so, he delivers a legally charged approval opinion. Any data provided must remain linked to the inspection.

[orange]

Thanks for the info.

Sounds like:

Request electric hookup by Client/Owner
ReceiveRequest (Inspector/Agent??)
ScheduleMeeting(s) (Inspector??/Other??)
InspectInstallation (Inspector and Others) (may need detailed tasks and acceptance??)
Approve (Document Inspection with meeting Notes)
If not approved(goto ScheduleMeeting)
---------------------
Who actual does the work of electrical hookup? (electrical contractor?)

You have identified several people who perform roles in the various interactions.

Role-----------------------+
PeopleDetails--->RoleInInteraction<-----InterAction<----PropertyInvolved
InterActionType-------------------------------^

[Edgar]

Thank you, orange.
I don't understand the nomenclature used in your reply but I'll try to answer with what I believe you are asking.

Request electric hookup by Client/Owner

First step is the request from client or owner, the electric hookup is none of our business.

ReceiveRequest (Inspector/Agent??)

The inspector handles the request via chat, phone, email, web form or in person. No intermediaries.

ScheduleMeeting(s) (Inspector??/Other??)

Before scheduling, the client/owner must send the project to the inspector. We can not proceed if there is no project. It is required to tell the client how much it'll cost and to know if it's inspection-able or not.
Once the client accepts the cost, we can schedule after receiving 50% of the cost. Done by the inspector.

InspectInstallation (Inspector and Others) (may need detailed tasks and acceptance??)

The inspection is done solely by the inspector. The people involved in the inspection are figures required by the norm and it can be the same person in all fields, except for the witness fields. But it does require agreement between signatory/inspector.

Approve (Document Inspection with meeting Notes)
If not approved(goto ScheduleMeeting)

We must first make sure 100% of the money has been paid before, but yes.

Who actual does the work of electrical hookup? (electrical contractor?)

Not the job of the inspector, it's up to the client.

You have identified several people who perform roles in the various interactions.

Role-----------------------+
PeopleDetails--->RoleInInteraction<-----InterAction<----PropertyInvolved
InterActionType-------------------------------^

The roles and interactions are not really important as the work is done solely by the inspector. Storing the people with their data is what matters.

[orange]

1 The client or owner requests electricity --to the inspector

2 The inspector "handles" the request via chat, phone, email, web form or in person. No intermediaries.

3 A Project is defined and initiated and sent to the Inspector.
You need a definition of a Project (who,what,when,where,how much and how often..)

These points must be clearly identified. What exactly is it (in the it'll)

"Before scheduling, the client/owner must send the project to the inspector. We can not proceed if there is no project. It is required to tell the client how much it'll cost and to know if it's inspection-able or not.
Once the client accepts the cost, we can schedule after receiving 50% of the cost. Done by the inspector."

4 We must first make sure 100% of the money has been paid before (the inspection), but yes.

5 The inspection is done solely by the inspector. The people involved in the inspection are figures required by the norm and it can be the same person in all fields, except for the witness fields. But it does require agreement between signatory/inspector.


6 Somewhere, the client must arrange with a third party the actual physical work. This is not part of the Inspector's work.

7 Once inspection has been approved, all documentation including witnesses and related details, is recorded with the inspection data.

The roles and interactions are not really important as the work is done solely by the inspector. Storing the people with their data is what matters.

But your potential data models in post #7 above has an abundance of people and roles. This cannot be unimportant if your model is anywhere reflective of the business.