If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
In conflict with my architect
My current client has assigned an architect to design the data model for our
application. Our app requires a list of resources (people assigned to projects). Our app also requires a list of users (people who use the application). In the past, I've always created two separate tables for these two entities. Along comes our architect and he is obliging me to put the resources and the users in the same table! His view on this is that resources and users are "people" so they belong in the same table. I don't agree with him because there are fields that pertain to resources, while other fields pertain to users. The validation rules are different for both as well. He asked me to create two separate "list" forms for these entities. One listing resources and the other listing users. The problem is that all users ARE resources as well. So when you open the resources list, you don't see the users. Anyone using my application might assume that there is a resource missing, so he/she might add a new resource by accident, when all along the person already exists as a user. In the past, when I've been in a similar situation, I've always had, for example, a Resources table and a Users table. In the Users table I had a foreign key that linked to the Resources table. My queries were cleaner, my forms were cleaner and better organized. I'd like to have your opinions on this. Including anyone out there that agrees with the architect. |
#2
|
|||
|
|||
In conflict with my architect
The decision is your clients to make. Explain that you can follow the
architect's model and produce an inferior product or follow your own model and produce a superior product. Which do they want? "Ray C" wrote: My current client has assigned an architect to design the data model for our application. Our app requires a list of resources (people assigned to projects). Our app also requires a list of users (people who use the application). In the past, I've always created two separate tables for these two entities. Along comes our architect and he is obliging me to put the resources and the users in the same table! His view on this is that resources and users are "people" so they belong in the same table. I don't agree with him because there are fields that pertain to resources, while other fields pertain to users. The validation rules are different for both as well. He asked me to create two separate "list" forms for these entities. One listing resources and the other listing users. The problem is that all users ARE resources as well. So when you open the resources list, you don't see the users. Anyone using my application might assume that there is a resource missing, so he/she might add a new resource by accident, when all along the person already exists as a user. In the past, when I've been in a similar situation, I've always had, for example, a Resources table and a Users table. In the Users table I had a foreign key that linked to the Resources table. My queries were cleaner, my forms were cleaner and better organized. I'd like to have your opinions on this. Including anyone out there that agrees with the architect. |
#3
|
|||
|
|||
In conflict with my architect
Ray C wrote in
: My current client has assigned an architect to design the data model for our application. Our app requires a list of resources (people assigned to projects). Our app also requires a list of users (people who use the application). In the past, I've always created two separate tables for these two entities. Along comes our architect and he is obliging me to put the resources and the users in the same table! His view on this is that resources and users are "people" so they belong in the same table. I absolutely agree. If you have two tables that have the same columns, then you shouldn't have two tables. You'd then have some way of assigning a role to each person, and if they can only have one at a time, you can have a filed in the people table that assigns the role. If they can have more than one, then you need a 1:N table with their roles (it's actually a many-to-many join table with the table listing the roles). This is absolutely the correct architecture, in my opinion. I adopted this approach a long time ago and it simplifies a huge number of issues. I don't agree with him because there are fields that pertain to resources, while other fields pertain to users. The validation rules are different for both as well. Perhaps you need super- and subtype tables, i.e., a people table with the common fields and 1:1 tables with the attributes that apply only to each of the subtypes. You then would know which is which based on whether or not there's a record in the subtype table. He asked me to create two separate "list" forms for these entities. One listing resources and the other listing users. The problem is that all users ARE resources as well. So when you open the resources list, you don't see the users. Anyone using my application might assume that there is a resource missing, so he/she might add a new resource by accident, when all along the person already exists as a user. That's a bad UI implementation, not a schema problem. In the past, when I've been in a similar situation, I've always had, for example, a Resources table and a Users table. In the Users table I had a foreign key that linked to the Resources table. My queries were cleaner, my forms were cleaner and better organized. It really depends on the amount of overlap of the fields, and the degree to which an individual is likely to be both or to change from one type to the other. From what you've described, I'd implement it as THREE tables, the people table, and two 1:1 tables for the attributes specific to each type. The joins would then be very easy to do. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#4
|
|||
|
|||
In conflict with my architect
mscertified wrote in
: The decision is your clients to make. Explain that you can follow the architect's model and produce an inferior product or follow your own model and produce a superior product. Which do they want? A competent programmer ought to be able to make both schemas work equally well. And the differences should be completely invisible to the end user. If the programmer can't do that, then the problem is not with the schema. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#5
|
|||
|
|||
In conflict with my architect
On May 16, 6:23 am, Ray C wrote:
My current client has assigned an architect to design the data model for our application. Our app requires a list of resources (people assigned to projects). Our app also requires a list of users (people who use the application). In the past, I've always created two separate tables for these two entities. Along comes our architect and he is obliging me to put the resources and the users in the same table! His view on this is that resources and users are "people" so they belong in the same table. I don't agree with him because there are fields that pertain to resources, while other fields pertain to users. The validation rules are different for both as well. He asked me to create two separate "list" forms for these entities. One listing resources and the other listing users. The problem is that all users ARE resources as well. So when you open the resources list, you don't see the users. Anyone using my application might assume that there is a resource missing, so he/she might add a new resource by accident, when all along the person already exists as a user. In the past, when I've been in a similar situation, I've always had, for example, a Resources table and a Users table. In the Users table I had a foreign key that linked to the Resources table. My queries were cleaner, my forms were cleaner and better organized. I'd like to have your opinions on this. Including anyone out there that agrees with the architect. I agree with the previous answer you received. The architect builds and designes structures but a bd is not that. You need to explain to the client the issue and what a relational db is. If they don't want to let you build it the proper way then I would get a waiver so you are not stuck later on fixing an issue that was identified as faulty up front. |
#6
|
|||
|
|||
In conflict with my architect
" wrote in
oups.com: If they don't want to let you build it the proper way then I would get a waiver so you are not stuck later on fixing an issue that was identified as faulty up front. The architect is the one proposing the proper way to build the database. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
In conflict with my architect
"David W. Fenton" wrote in message . 1... Ray C wrote in : My current client has assigned an architect to design the data model for our application. Our app requires a list of resources (people assigned to projects). Our app also requires a list of users (people who use the application). In the past, I've always created two separate tables for these two entities. Along comes our architect and he is obliging me to put the resources and the users in the same table! His view on this is that resources and users are "people" so they belong in the same table. I absolutely agree. If you have two tables that have the same columns, then you shouldn't have two tables. You'd then have some way of assigning a role to each person, and if they can only have one at a time, you can have a filed in the people table that assigns the role. If they can have more than one, then you need a 1:N table with their roles (it's actually a many-to-many join table with the table listing the roles). This is absolutely the correct architecture, in my opinion. I adopted this approach a long time ago and it simplifies a huge number of issues. I don't agree with him because there are fields that pertain to resources, while other fields pertain to users. The validation rules are different for both as well. Perhaps you need super- and subtype tables, i.e., a people table with the common fields and 1:1 tables with the attributes that apply only to each of the subtypes. You then would know which is which based on whether or not there's a record in the subtype table. He asked me to create two separate "list" forms for these entities. One listing resources and the other listing users. The problem is that all users ARE resources as well. So when you open the resources list, you don't see the users. Anyone using my application might assume that there is a resource missing, so he/she might add a new resource by accident, when all along the person already exists as a user. That's a bad UI implementation, not a schema problem. In the past, when I've been in a similar situation, I've always had, for example, a Resources table and a Users table. In the Users table I had a foreign key that linked to the Resources table. My queries were cleaner, my forms were cleaner and better organized. It really depends on the amount of overlap of the fields, and the degree to which an individual is likely to be both or to change from one type to the other. From what you've described, I'd implement it as THREE tables, the people table, and two 1:1 tables for the attributes specific to each type. The joins would then be very easy to do. I agree with you to the extent that the architect was right and that it probably warrants more than two tables, but depending on the actual data requirements, I would probably do it this way (four tables): Table 1: People Table 2: Roles Table 3: RoleProperties Table 4: PeopleRoleProperties So, in the first table, you define who the people are. In the second table, you define what roles are possible (allows for scalability in number of roles). In the third table, you define what properties are possible for a given role (allows scalability in how many properties you want to assign without adding more fields). In the fourth table, you define what properties a given person has _when in a given role_ (assign actual values to the properties that role could have as defined in RoleProperties). The only issue is that you would have to provide validation at the form level to make sure that the proper data type was used when filling in the property value. This structure might also imply a fifth table, RolePropertyValues, to provide lookup values for comboboxes and such to populate PeopleRoleProperties. HTH; Amy |
#8
|
|||
|
|||
In conflict with my architect
Let me add my 2 cents here also. The architect is correct. All people
belong in the same table. You then need two other tables. One to define roles and the second to relate people to roles. You then create separate queries to return "users" by using criteria against the role or "resources". Then use the queries as you would have used the separate tables. The single "person" table minimizes the potential for future change (since you can now add additional people types by simply adding a new row to the role table and a new query to return that specific role type) and it eliminates any duplication caused by overlapping roles. Try it the architects way. You'll like it "David W. Fenton" wrote in message . 1... " wrote in oups.com: If they don't want to let you build it the proper way then I would get a waiver so you are not stuck later on fixing an issue that was identified as faulty up front. The architect is the one proposing the proper way to build the database. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#9
|
|||
|
|||
In conflict with my architect
"Amy Blankenship" wrote in
: I agree with you to the extent that the architect was right and that it probably warrants more than two tables, but depending on the actual data requirements, I would probably do it this way (four tables): Table 1: People Table 2: Roles Table 3: RoleProperties Table 4: PeopleRoleProperties So, in the first table, you define who the people are. In the second table, you define what roles are possible (allows for scalability in number of roles). In the third table, you define what properties are possible for a given role (allows scalability in how many properties you want to assign without adding more fields). In the fourth table, you define what properties a given person has _when in a given role_ (assign actual values to the properties that role could have as defined in RoleProperties). The only issue is that you would have to provide validation at the form level to make sure that the proper data type was used when filling in the property value. This structure might also imply a fifth table, RolePropertyValues, to provide lookup values for comboboxes and such to populate PeopleRoleProperties. That's what I'd call an overnormalized structure. Basically, you could replace *all* tables in any database schema with a table for properties, having 3 columns, a PK Autonumber, a property type and a value field. But that would just be silly. Your suggestion is an implemtnation of that approach on a small scale, and seems to me to be, well, silly. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#10
|
|||
|
|||
In conflict with my architect
"David W. Fenton" wrote in message . 1... "Amy Blankenship" wrote in : I agree with you to the extent that the architect was right and that it probably warrants more than two tables, but depending on the actual data requirements, I would probably do it this way (four tables): Table 1: People Table 2: Roles Table 3: RoleProperties Table 4: PeopleRoleProperties So, in the first table, you define who the people are. In the second table, you define what roles are possible (allows for scalability in number of roles). In the third table, you define what properties are possible for a given role (allows scalability in how many properties you want to assign without adding more fields). In the fourth table, you define what properties a given person has _when in a given role_ (assign actual values to the properties that role could have as defined in RoleProperties). The only issue is that you would have to provide validation at the form level to make sure that the proper data type was used when filling in the property value. This structure might also imply a fifth table, RolePropertyValues, to provide lookup values for comboboxes and such to populate PeopleRoleProperties. That's what I'd call an overnormalized structure. Basically, you could replace *all* tables in any database schema with a table for properties, having 3 columns, a PK Autonumber, a property type and a value field. But that would just be silly. Your suggestion is an implemtnation of that approach on a small scale, and seems to me to be, well, silly. It's actually quite useful sometimes... |
|
Thread Tools | |
Display Modes | |
|
|