To Union or Not, that is the question that preoccupies us.

[Thales750]

Morning All,
This is a philosophical question, although I suspect the roots of this inquiry lie in performance.
I have a table whose "lot in life" is to manage formal request from the field to do a specific job. That’s the easy part.
Management wants to incorporate a task management list as well and they want it to be in the same view (subform).
So I could add a couple of fields to the tblRequest and it will work for both the formal request and the daily ancillary task that the staff need to perform.
Which would you do; make a separate table and Union Join them in a subform or create more fields in the Request table to manage the extra task?
I’m not sure there’s a “right answer” here, I think it will depend on which method will produce the highest performance over the long run.

 

[Banana]

Hmm. I'm not sure if I'm following you.

Are you thinking that the task management should be a part of tblRequests? Who can be assigned/de-assigned/sharing tasks?

If so, then I would say this should be a separate table because you have a many-many relationship between tasks and doers, so you need a junction tables to model this relationship as well to track the time component so a task can be assigned to only one person at one time but over the time assigned to several different people over its lifetime.

If I've not understood the question, my apologies. Maybe a structure of table & proposed change would help?

 

[Thales750]

It has to be separate. Either they are combined in a union join or on two separate subforms.

Thanks Banana, for the question.

 

[Banana]

I'm sorry but that was even more confusing.

What is 'it' that has to be separate? Why does it have to be either unioned or on two subforms?

The reason why it's confusing is this:

1) It would be nonsensical to union two different tables (e.g. Tasks requests and Task Management listing who's assigned to what task). Normally, we do a union between two tables of similar structure, or maybe for the same table to use different criteria, but not to combine two tables of different content.

2) If there's going to be two subforms, it begs the question of what would be the third entity; we've been talking about two things, tasks and assignments. I suppose they could be dropped into a pair of subform side by side on a unbound form, but this is still a case where more context is needed.

3) As indicated before, a listing of the structure would do wonder in help understanding why this being asked at all because to be honest, I'm only see a simple parent-detail structure and feeling there's more to the story.

 

[namliam]

All information in a table should be bound to its primary key, if not it requires an additional table.

In this case your new information is not needed in a lot of cases (requests) but only for part of that list.
This would require IMHO a new table that you join to this one in the form... with a 1/0 relationship (basicaly a twisted 1:N) as not all will have extra information, while non will have more than 1.

 

[gemma-the-husky]

it all comes back to correct data analysis and normalisation - things that belong together should be stored together - things that dont should not be. this will lead to harmonious system development

so this comment

Management wants to incorporate a task management list as well and they want it to be in the same view (subform).

is strange per se, as it depends on what sorts of information you are showing

if you have tasks, of which one attribute is (say) a special indicator, then a single form can display all the tasks together with this attribute

however, if you have two generally different things then it is normally better to manage them separately, than try to force them together by unioning them. One problem with the union is that the data is immediately non-updateable

you could have on the mainform, a tab control with one page showing each of the different tasks

or you could have an option group to toggle the display between the two subsidiary information types.

 

[Thales750]

As it turns out; I elected to keep the data separate, and to control the view, in two separate subforms by having them both filtered by the same multiselect list boxes.
They will like it.
Thanks guys for being my sounding board.

 

[dportas]

There is actually a formal rule for evaluating such situations. Date and McGoveran's Orthogonal Design principle, "POOD" (see "Date on Database", Apress).

Roughly speaking POOD means it should never be possible for the same tuple (any proper superkey in fact) to be present in more than one place in the database. There are a few good reasons for eliminating redundancy of that kind, one obvious one being that it minimises the amount of potentially duplicated code in constraints and other business logic (DRY principle).

POOD is a useful design principle quite apart from Normalization. That is, it is perfectly possible for a database to be in 5th Normal Form and still not comply with POOD.

 

[Thales750]

My gut feeling is that like so many things in life there is not nessisarily a "one size fits all aproach"
Even the authors of that design philosophy agree with me.

Database design is more art than science, that is, it is mainly of an informal nature. Whatever formality is there, it has been limited to what we currently call the Principle of Full Normalization (POFN), expounded in The Costly Illusion: Normalization, Integrity and Performance (paper #3 in this series). In 1993, however, McGoveran and Date presented in a two-part paper an additional formal design principle, The Principle of Orthogonal Design (POOD). Jointly, the two principles are intended to prevent redundancy, update anomalies, harder to understand databases, and other practical complications in database design.

The POOD formally defined in the original paper was informally described as prohibition of tables with overlapping meanings. But there is now disagreement between the two authors on the original definition of POOD, which has been questioned by at least one (see On Non-Loss Decomposition); Date has since revised his formal definition in Data Redundancy and Database Design, Further Thoughts Part 1, but it is quite complex, it does not have a very easy informal description, and we are not sure about some of the assumptions behind it.

The purpose of this paper is to further clarify POOD, for a better understanding that would enable adherence with relative ease by the average practitioner.

However I will study it