In conflict with my architect

[Guest]

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.

 

[Guest]

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?

 

[David W. Fenton]

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]

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.

 

[darrin.wilson]

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.

 

[David W. Fenton]

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.

 

[Amy Blankenship]

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.


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.


That's a bad UI implementation, not a schema problem.


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

 

[Pat Hartman \(MVP\)]

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]

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.

 

[Amy Blankenship]

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

 

[AaronKempff]

If you have two tables that have the same
columns, then you shouldn't have two tables.

My current client has assigned an architect to design the data model
for our application. Our app requires a list of zoo resources (zoo
keepers assigned to animal enclosures). Our app also requires a list
of users (human visitors and their guide dogs who use the zoos). In
the past, I've always created separate tables for these entities.

Along comes our architect and he is obliging me to put the visitors,
zoo keepers, guide dogs and zoo animals in the same table! His view on
this is that they are all "animals" so they belong in the same table.

I don't agree with him because there are fields that pertain to zoo
animals, while other fields pertain to visitors. The validation rules
are different for both as well e.g. chimps are not permitted to take
tea in the refectory. How do I prevent a lower order primate from
being appointed as a zoo keeper?

 

[AaronKempff]

If you have two tables that have the same
columns, then you shouldn't have two tables.

My current client has assigned an architect to design the data model
for our application. Our app requires a list of zoo resources (zoo
keepers assigned to animal enclosures). Our app also requires a list
of users (human visitors and their guide dogs who use the zoos). In
the past, I've always created separate tables for these entities.

Along comes our architect and he is obliging me to put the visitors,
zoo keepers, guide dogs and zoo animals in the same table! His view on
this is that they are all "animals" so they belong in the same table.

I don't agree with him because there are fields that pertain to zoo
animals, while other fields pertain to visitors. The validation rules
are different for both as well e.g. chimps are not permitted to take
tea in the refectory. How do I prevent a lower order primate from
being appointed as a zoo keeper?

 

[Amy Blankenship]

My current client has assigned an architect to design the data model
for our application. Our app requires a list of zoo resources (zoo
keepers assigned to animal enclosures). Our app also requires a list
of users (human visitors and their guide dogs who use the zoos). In
the past, I've always created separate tables for these entities.

Along comes our architect and he is obliging me to put the visitors,
zoo keepers, guide dogs and zoo animals in the same table! His view on
this is that they are all "animals" so they belong in the same table.

I don't agree with him because there are fields that pertain to zoo
animals, while other fields pertain to visitors. The validation rules
are different for both as well e.g. chimps are not permitted to take
tea in the refectory. How do I prevent a lower order primate from
being appointed as a zoo keeper?

I'd say that is a different kettle of fish entirely. He may or may not be
right about some of it.

For instance, I can see an advantage to treating zookeepers as animals,
since they can be assigned to enclosures. However, it is probably more
useful to put all of the people in one table, regardless of if they are
employees or visitors. The zoo animals should probably have their own table
as well. I suspect that the guide dogs should be in their own table, not in
with the zoo animals or the people, since they alone of all the entities
will have an owner that is in the people table. What is VERY apparent is
that your visitors should not be treated as zoo animals, because they won't
be assigned to an enclosure (unless you want to invent the fiction of a
visitor enclosure).

HTH;

Amy

 

[Pat Hartman \(MVP\)]

I don't believe that animals belong in the people table. Chimps are
definitely human-like and we share many attributes but I resent being put
into the same table as a snake.

Roles are insufficient to separate people from animals. You will need code
fields on each record to identify human vs. animal and you will need
separate class tables to hold their unique attributes.

 

[David W. Fenton]

I don't believe that animals belong in the people table.

You are all victims of one of Aaron's jokes. He's not serious at all
-- he never posts anything serious in any of the Access groups. All
he ever does is attempt to disrupt discussions by posting erroneous
information.

 

[Amy Blankenship]

You are all victims of one of Aaron's jokes. He's not serious at all
-- he never posts anything serious in any of the Access groups. All
he ever does is attempt to disrupt discussions by posting erroneous
information.

Hadn't you heard? A pig is a rat is a dog is a boy...

 

[John W. Vinson]

Hadn't you heard? A pig is a rat is a dog is a boy...

And all animals are equal...

but some are more equal than others.

John W. Vinson [MVP]