Re: Advice about a parent-child relation design

On 22/11/15 06:45, JORGE MALDONADO wrote:
> I have 2 tables: parent and child. One of the fields, which should
> supposedly be part of the parent table, might occassionaly be
> different for one of the child table records.
>
> ParentTable
> * id
> * date
> * customer <-----
> * reference
> * other fields...
>
>
> ChildTable
> * id-parent
> * document type
> * customer <-----
>
> It is possible that the customer field is not the same for all of the
> child records. This is a very specific (and strange) situation that
> might eventually appear in the information system I am developing. I
> suppose that such a field must be part of the child table even if this
> case does not appear frequently.
>
> I am writing to ask for your feedback and see if there is another
> possible approach.
>
> With respect,
> Jorge Maldonado

Hi Jorge,

I suggest you rename the 'customer' column in the child record, to
something like 'alternate-customer' (or something more meaningful &
relevant to your use case), as it is confusing at the moment.

If I understand it: normally when you process the child record, you will
be using the 'customer' column of the parent, except when it is
different. One way of modelling this is to set 'alternate-customer' (or
whatever you choose to rename the child customer column too) to null,
unless it is to be used instead of the parent 'customer' column.

I think otherwise it is a reasonable design, from what little I know
about your situation.

Cheers,
Gavin