Thread: Re: "Reverse" inheritance?

Re: "Reverse" inheritance?

From
vinny
Date:
I agree with the barking up the wrong tree, building a physical tree in
tables doesn't sound right
given that you will have to create a new branch in the tree when a new
version/variation of ubuntu comes out.

Also think about how you are going to do basic queries like listing all
known unix variants; if that is hidden in the table names
then you'll have to issue DDL queries to do the work of SELECT queries,
which just sounds wrong to me.

I'd go for a tree, possibly using recursive CTE's to dig it.

On 2017-04-04 05:19, Tim Uckun wrote:
> I have thought of doing something like a single table inheritance and
> it
> could be done but I thought this might be a little more elegant.
>
> On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun <timuckun@gmail.com> wrote:
>>
>>> I am trying to make postgres tables work like an object hierarchy. As
>>> an
>>> example I have done this.
>>>
>>
>> ​I suspect you are barking up the wrong tree ;)
>>
>> You are probably better off incorporating something like the "ltree"
>> type
>> to encode the taxonomy.
>>
>> https://www.postgresql.org/docs/current/static/ltree.html
>>
>> I haven't had a chance to leverage it myself but the concept it
>> embodies
>> is solid.
>>
>> David J.
>> ​
>>


Re: "Reverse" inheritance?

From
Tim Uckun
Date:
>I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right
given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out.

This doesn't bother me that much. If can say create table ubuntu_17_04 inherits ubuntu and have it return all the parents data as I described it would be awesome

>Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table names
then you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me.

Yes this might be a problem but one I could easily overcome.

>I'd go for a tree, possibly using recursive CTE's to dig it.

I was thinking a window function but yea I am sure there is a way to do it with a flat table.

On Tue, Apr 4, 2017 at 6:43 PM, vinny <vinny@xs4all.nl> wrote:
I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right
given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out.

Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table names
then you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me.

I'd go for a tree, possibly using recursive CTE's to dig it.


On 2017-04-04 05:19, Tim Uckun wrote:
I have thought of doing something like a single table inheritance and it
could be done but I thought this might be a little more elegant.

On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun <timuckun@gmail.com> wrote:

I am trying to make postgres tables work like an object hierarchy. As an
example I have done this.


​I suspect you are barking up the wrong tree ;)

You are probably better off incorporating something like the "ltree" type
to encode the taxonomy.

https://www.postgresql.org/docs/current/static/ltree.html

I haven't had a chance to leverage it myself but the concept it embodies
is solid.

David J.



Re: "Reverse" inheritance?

From
vinny
Date:
On 2017-04-04 09:12, Tim Uckun wrote:
>> I agree with the barking up the wrong tree, building a physical tree
> in tables doesn't sound right
> given that you will have to create a new branch in the tree when a new
> version/variation of ubuntu comes out.
>
> This doesn't bother me that much.

It should. You are using tables as data, which is pretty much always a
smell of bad design.
I could be pedantic and ask how you would store unix version "14.5
\%funky penguin%/ rev 1,5"
given that most of that name consists of characters that are not allowed
in a table name.

>
>> Also think about how you are going to do basic queries like listing
> all known unix variants; if that is hidden in the table namesthen
> you'll have to issue DDL queries to do the work of SELECT queries,
> which just sounds wrong to me.
>
> Yes this might be a problem but one I could easily overcome.

Sure, but why would you though? You already have everything in place for
creating records,
why bother creating a different system just for the unix versions?


>
>> I'd go for a tree, possibly using recursive CTE's to dig it.
>
> I was thinking a window function but yea I am sure there is a way to
> do it with a flat table.

I'm not sure you can do it with windowing actually,
given that you'd have to sort every record based on a match with the
previous record.
But I've never tried it because CTE's make it so easy :-)

>
> On Tue, Apr 4, 2017 at 6:43 PM, vinny <vinny@xs4all.nl> wrote:
>
>> I agree with the barking up the wrong tree, building a physical tree
>> in tables doesn't sound right
>> given that you will have to create a new branch in the tree when a
>> new version/variation of ubuntu comes out.
>>
>> Also think about how you are going to do basic queries like listing
>> all known unix variants; if that is hidden in the table names
>> then you'll have to issue DDL queries to do the work of SELECT
>> queries, which just sounds wrong to me.
>>
>> I'd go for a tree, possibly using recursive CTE's to dig it.
>>
>> On 2017-04-04 05:19, Tim Uckun wrote:
>> I have thought of doing something like a single table inheritance
>> and it
>> could be done but I thought this might be a little more elegant.
>>
>> On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun <timuckun@gmail.com>
>> wrote:
>>
>> I am trying to make postgres tables work like an object hierarchy.
>> As an
>> example I have done this.
>>
>> ​I suspect you are barking up the wrong tree ;)
>>
>> You are probably better off incorporating something like the "ltree"
>> type
>> to encode the taxonomy.
>>
>> https://www.postgresql.org/docs/current/static/ltree.html [1]
>>
>> I haven't had a chance to leverage it myself but the concept it
>> embodies
>> is solid.
>>
>> David J.
>> ​
>
>
>
> Links:
> ------
> [1] https://www.postgresql.org/docs/current/static/ltree.html