I have two working structures for a database I am beginning to create. One is very granular, and I think the more theoretical approach, and from what I understand of normalization, 3NF. The other structure seems more practical, and could be considered 3NF depending on interpretations. The fields in the tables are just working fields and are not final, and this is just the base structure, much more is planned for data involving employees, calculating payroll, and jobsite information.
Which one would be the best to pursue?
Description: This is for a cleaning service. Service is provided to domestic and commercial job sites (homes and businesses). Each jobsite can have many clients (contacts) that need to be tracked for various reasons (residents, billing, emergency) and these clients can also have different phones also. And some clients can have many jobsites (landlord, apt complex, client paying for personal and relative’s services). Each jobsite has only one address/physical location. 70% jobsites have only one contact and one address, 90% have one address and 2 contacts.
The less complicated diagram is based on following idea. The physical location and jobsite landlines are stored with the jobsite (if relevant), perhaps even a main contact name and personal info. All other contacts are related to the jobsite, and only when information is different than the jobsite information, would data be entered (or duplicated for off-site contacts – a possibility)). This seems conceptually simple enough, not complicated, and easier to manage. And might be considered 3NF if one considers addresses different than the jobsite to be different entities. I know that working with the more granular approach, it was a pain to manually enter data and navigate two junction tables, but I figure that could be rectified with forms, but I couldn’t say how complex those forms might have to be. When I run a query for prices based on names, I do get the same results for both structures, so I least I am duplicating some end results so far.
I am relearning Access. The last time I tried to learn, just did not have things like Reddit, youtube, many discussion groups, Udemy and so far I have jumped farther and faster than just reading books….). I am still rusty on forms and never got to reports and did little with queries. I do not know SQL, but am willing to learn it. I do not want the database to just be an ad hoc of data either, and I hope I could give this database to other people for them to use in their own business (other small franchise). I would also like a structure that would be less tedious to work with when creating reports, queries and forms. Please offer your suggestions.
simple one
too granular?
