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.

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.

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/

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/

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.

" 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/

"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

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/

"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/

"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...