Hello everyone,
QUESTION HOW WOULD YOU DESIGN THE DATABASE? OPTION ONE: If I eager load Member page with all the relations the view page would run 10 SQL queries total on load to display the data. When a member wants to update a record, they would click on the button next to either the phone section, name section and that would create a new record on the respective table and refresh the page to show the latest change.
OPTION TWO: If I have all the fields on the Member table, it would run only 4 SQL queries. When a member wants to update a record, they would click on a button next to the field they want to edit, when it saves, it would generate a record in the respective table AND update the field on the main Members table. (In this case the Model Member would have about 23 fields)
DESIGN AND REQUIREMENTS I would like to keep a history of changes that are made for every Member for their address, phones, emails. By creating different tables for each one of these we can keep track of when the change was made, who made it and more.
RELATIONSHIPS Users hasOne Member. Member belongsTo User Member hasMany Phones. Phones belongTo Member. (I have 6 Names, Phones, Addresses, Marital, Status, Emails) Thank you for your tips and recommendations in advanced!
Why would there be 10 queries; if you have the appropriate relationships setup, then there are 3 queries?
User::with('member.phones')->find($id);
I would consider denormalizing; it seemsthat a User is a Member. So, moving the member columns onto the `users_ table might make sense, but I don't know you exact use case, so can't be absolute.
I would like to keep a history of changes
Spatie has a package for that
I would go with separating the information into different tables where it makes sense, and I would create a database view to fetch the information, and in the case of a one to many I would eager load.
However it all depends on the data and what you want to do with it. In general I go with the simple approach.
users contains all you need to authenticate the user.
user_profiles contains the users profile.
If I have a set number of phone numbers for the user, I would include that in a user_contacts table, but it a user can have an unlimited number of phone numbers, then I would extract that to it's own table.
I suggest giving this one a read.
All of these tables are one to many as the goal is to always keep track of changes for everything on the users. I decided to create a table for each one of these as users will be grant access to add as many records as needed.
Thank you for the link and tips!
Please or to participate in this conversation.