Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Is this bad design ?

Re: Is this bad design ?

From: ben brugman <>
Date: Thu, 11 Mar 2004 11:50:02 +0100
Message-ID: <4050445a$0$6565$>

Summerization of the mails (so far).

First the 'problem' was a stripped down version of the 'real' problem. There are more tables, more fields and more relations. The stripping down was done to keep the example easier to read and still contain the 'problem'. This partly failed, because suggestions were given based on the simplisity of the example. But the child is a more complex object, having its own relation to other objects. (To keep to the terminologie, that could be grandchildren, fathers and teachers).

(The "valid attribute" is an attribute which exists in the real world. Users using 'mothers' do know the meaning of a valid and non-valid mother).

Suggestion A:
Splitting the table in two were one table only holds the valid mothers and one table holds the non-valid mothers. This would mean a change in applications, a view would not work in my oppinion, because not all mutations are possible through a view. (For example the change from valid to non valid, but also other mutations). If there are more of these booleans. The splitting up process becomes complex. (If there are 3 'valid' booleans, we would end up with 8 tables.).

Suggestion B:
Including the children in the data of the mother. The children are more complex than represented in the example. (A child has relations with other objects as wel, call them teachers in this example). So this is not possible. (The example was to 'simple' to discern this).

Suggestion C:
Leaving out the "valid" attribute, because it is a real world attribute, the attribute has to be implemented. (One way or another).

Suggestion D:
"But, if database access is encapsulated in one class/package/module in your application, it should be possible to change the database schema without too much side effects? "
If a design depends on encapsulation in one class/package/module is this not considered bad design ?
This is/was one of the suggestions for implementation. The constraint check was also done in the application. We did run into problems with Oracle, because of the read consistency the rows which were not updated by the application could be changed by another transaction. In SQL-server the transaction had to run on the 'serializable level', a level not advised by several institutions and persons. Because of lack of experience with this level (at that time) we decided against this. (I still do not know if this was a right decision or not).

Suggestion E:
In the end we came with the following solution. We used a changenumber in the mother for all updates on the mother and her children and grandchildren.
In our case this works, because the 'mother' is such an important object on our design.
But an argument against this would be, what to do if we want to do a change on the children but this is based on a path were the mother is not the main object. For example a 'teacher' of the child construction. (Ok this is a bit fuzzy).
As said in our implementation the mother is CENTRAL (important) that mutating the child involves 'locking with an changenumber' the mother worked very well. (And we do not need to lock the teacher of the child.) But if you want to mutate a lot with this type of constructions and you need more
than one resource (mother, teacher etc) then there is a problem. So our actual implementation (different from the suggestion in the first mail)
works, but I am not totally happy with it.

Suggestion X:
Human brain organisation. Sorry but I do not think this contributes to a solution of the problem.
(Remark: The human memory is not designed to 'remember' acurately, but to survive. This means if alterations of 'memory' does aid in survival, the human brain has no problem with these alterations and so it sometimes does.).

Conclusion :
For the given problem I have not seen a design which can be implemented, (Oracle and SQL-server) which is not considered a bad design.

Thanks to all who contributed to this thread.

Ben Brugman.

"ben brugman" <> wrote in message news:404c9b34$0$281$
> Mother table.
> fields:
> mother_id
> still_valid boolean
> etc_fields
> Child table
> fields:
> mother_id
> still_valid boolean
> more_fields
> The child.still_valid field always contains a true, because only mothers
> withs are still_valid can have children, to enforce this; a relation is
> defined on the fields mother_id and still_valid.
> (A child can not exist without its mother).
> Because a field in the child table always has the same value, I think
> there is something wrong. But if this field is taken away a mother might
> become un_valid by another action.
> Is this bad design ?
> what is a alternative ?
> (As I understand constraints should be implemented in the RDBMS and
> not in the code).
> ben brugman
Received on Thu Mar 11 2004 - 04:50:02 CST

Original text of this message