Database design
-
I'm hoping someone can shed some light. I need to create a database with Employees, Clients, and Practitioners. An employee could be a client and a practitioner could also be a client. I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables. I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table. Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times. Any constructive feedback is appreciated. digicd1
-
I'm hoping someone can shed some light. I need to create a database with Employees, Clients, and Practitioners. An employee could be a client and a practitioner could also be a client. I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables. I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table. Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times. Any constructive feedback is appreciated. digicd1
You could create a generic table that contains people, and then Employee, Client, and Practitioner tables that have a "personID" and then the unique information for each.
I wasn't, now I am, then I won't be anymore.
-
I'm hoping someone can shed some light. I need to create a database with Employees, Clients, and Practitioners. An employee could be a client and a practitioner could also be a client. I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables. I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table. Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times. Any constructive feedback is appreciated. digicd1
digicd1 wrote:
I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables.
So, you're absolutely sure that every practitioner uses the same phone-number if they become clients? No practitioner who separates their workphone from their private phone?
digicd1 wrote:
I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table.
That would lead to a whole lot of joins to get a single record. How about a table for People, one for Clients, one for Practitioners and one for Employees?
digicd1 wrote:
Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times.
Normalize the model. This time, there's no viable alternative that I can come up with :)
I are Troll :suss:
-
digicd1 wrote:
I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables.
So, you're absolutely sure that every practitioner uses the same phone-number if they become clients? No practitioner who separates their workphone from their private phone?
digicd1 wrote:
I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table.
That would lead to a whole lot of joins to get a single record. How about a table for People, one for Clients, one for Practitioners and one for Employees?
digicd1 wrote:
Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times.
Normalize the model. This time, there's no viable alternative that I can come up with :)
I are Troll :suss:
I agree, that is alot of tables and it would only be the beginning. If a practitioner was a client then I would probably have two addresses. Once for work as a practitioner and one for his home as a client. If an employee was a client, I would have a home/work address for this individual as an employee and a home address as a client. Since the home address repeats itself - I could minimize duplicate records by having an address table vs. having the user be responsible to change the home address in two different locations. Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
-
I agree, that is alot of tables and it would only be the beginning. If a practitioner was a client then I would probably have two addresses. Once for work as a practitioner and one for his home as a client. If an employee was a client, I would have a home/work address for this individual as an employee and a home address as a client. Since the home address repeats itself - I could minimize duplicate records by having an address table vs. having the user be responsible to change the home address in two different locations. Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
digicd1 wrote:
Since the home address repeats itself - I could minimize duplicate records by having an address table
He'd still be a client once, and a practitioner once. You'd need a link to both addresses. The address might be registered and linked to more than once. That'll make complicated queries, and I doubt that you'll save a lot of room. It's a form of premature optimization, don't do it.
digicd1 wrote:
having the user be responsible to change the home address in two different locations
If it's two different entities, then they shouldn't be changed simultanously. Likewise, you could add a boolean to the Employee to let him/her indicate that their address as a client is the same address as their Employee-address. Depending on the boolean, you update either one, or both addresses.
digicd1 wrote:
Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
I think that storage-space is cheaper than cpu-time. You should really dive into normalization, three guidelines would help you get a relational model that you can build on. Once you have that, it's easier to spot where you can optimize things.
I are Troll :suss:
-
I'm hoping someone can shed some light. I need to create a database with Employees, Clients, and Practitioners. An employee could be a client and a practitioner could also be a client. I am hoping to design the database in a way that I only have to maintain name, demographic information, phone numbers, and emails once vs. having to change an address in multiple tables. I thought about having an entity table with the names, email table, phone table, address table, and then a table for each specific information such as a client table, emp table, and practioner table. Any thoughts on what the tables should look like to accomdate or is it better to have the users maintain the information multiple times. Any constructive feedback is appreciated. digicd1
Tables People Address PeopleType lnkPeopleType - many to many link between people and type (client can also pe a practitioner) AddressType attribute on address (you may want multiple links if you need to have the same address for 2 types) This is a heavily normalised structure and is a pain to use so create a view based on the PeopleType.
Never underestimate the power of human stupidity RAH
-
digicd1 wrote:
Since the home address repeats itself - I could minimize duplicate records by having an address table
He'd still be a client once, and a practitioner once. You'd need a link to both addresses. The address might be registered and linked to more than once. That'll make complicated queries, and I doubt that you'll save a lot of room. It's a form of premature optimization, don't do it.
digicd1 wrote:
having the user be responsible to change the home address in two different locations
If it's two different entities, then they shouldn't be changed simultanously. Likewise, you could add a boolean to the Employee to let him/her indicate that their address as a client is the same address as their Employee-address. Depending on the boolean, you update either one, or both addresses.
digicd1 wrote:
Do you think the extra table and joins would be a huge performance impact and would be better to store twice?
I think that storage-space is cheaper than cpu-time. You should really dive into normalization, three guidelines would help you get a relational model that you can build on. Once you have that, it's easier to spot where you can optimize things.
I are Troll :suss:
Eddy, Thank you for your reply. I think this answers my question. In summary it appears that using a database that is too normalized would really affect cpu time. And in an age where disk is cheaper, it would be better to have some information duplicated vs. slowing the entire system down. Chad
-
Eddy, Thank you for your reply. I think this answers my question. In summary it appears that using a database that is too normalized would really affect cpu time. And in an age where disk is cheaper, it would be better to have some information duplicated vs. slowing the entire system down. Chad
digicd1 wrote:
Thank you for your reply
My pleasure, I hope you don't mind me rambling on about the importance of normalizing too much. It may eat away a bit of your time, but it's well worth the investment.
digicd1 wrote:
In summary it appears that using a database that is too normalized would really affect cpu time.
A database can't be "too" normalized. A "good" level of normalization would be BCNF[^]. That should give you a correct model, without (much) redundancy. Keep in mind that most RDBMS-es are optimized to work with relational data, in a relational format.
digicd1 wrote:
it would be better to have some information duplicated vs. slowing the entire system down
That sounds like a statement on optimization again :)
I are Troll :suss: