Re: Is this bad design ?

From: Fabian Buettner <green_at_tzi.de>
Date: Tue, 09 Mar 2004 17:36:13 +0100
Message-ID: <404DF27D.2070801@tzi.de>

... your initial question was: is this bad design? It think it is. It is redundant and error prone. If you cannot change the database schema, you must live with your design. 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? Instead of setting still_valid to true/false you must insert(if non-existing)/delete a row in ValidMother. This may be realized as a stored procedure. I would say that adding constraints to a database schema often has impact on the application level if normalization/redundancy is an issue (thats why db access should be encapsulated).

Regards,
Fabian

ben brugman wrote:
> "Fabian Buettner" <green_at_tzi.de> wrote in message
> news:404DC817.2090804_at_tzi.de...
>

>>If only valid mothers can have children, you could partition your Mother
>>table as follows:
>>
>>Mother( mother_id primary key, etc_fields )
>>-- all mothers
>>
>>ValidMother( mother_id primary key + foreign key ref Mother(mother_id))
>>-- "valid mother" ids
>>
>>Child( mother_id foreign key ref. ValidMother(mother_id), more_fields)
>>
>>This way you can ensure that only valid mothers are referenced by
>>children. Also (if referential integrity is available), a mother can not
>>become invalid (i.e., delete entry from ValidMother) when there are
>>still children referencing it.

>
>
> But then the application has to be changed. (The databasemodel changes).
> And changing an application because of a constraint was / is (by some)
> considered as not preferable.
> (For selections a view would be possible, but for inserts/updates/deletes
> this would not work).
>
> (At the moment we use a software lock on the mother to prevent
> a change in any of the mothers children (grandchildren).
> The software lock is build by using a changenumber, and anybody
> altering the mother or children (or grandchildren) has to use
> the software lock on the mother.
> But I am not satisfied with this solution either.)
>
> ben brugman
>

>
Received on Tue Mar 09 2004 - 10:36:13 CST