Postgres Pro
Downloads
Home   >   mailing lists

Thread: Advice about a parent-child relation design

Advice about a parent-child relation design

From
JORGE MALDONADO
Date:
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

Re: Advice about a parent-child relation design

From
Felipe Santos
Date:


Em 21/11/2015 3:46 PM, "JORGE MALDONADO" <jorgemal1960@gmail.com> escreveu:
>
> 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,

One question: since the Child table has  the parent  id , why do you need to replicate the customer information ?

Regards

Re: Advice about a parent-child relation design

From
Gavin Flower
Date:
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

Re: Advice about a parent-child relation design

From
Gavin Flower
Date:
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

This might be useful (see my other post)

SELECT
     COALESCE(c.alternate_customer, p.customer)
FROM
     child c,
     parent p
WHERE
     p.id = c.id;


Cheers,
Gavin