[Engine-devel] What type of DB inheritance to use?

[Eli Mesika]

----- Original Message -----
> From: "Liran Zelkha" <liran.zelkha at gmail.com>
> To: "Yair Zaslavsky" <yzaslavs at redhat.com>
> Cc: "engine-devel" <engine-devel at ovirt.org>
> Sent: Sunday, May 12, 2013 1:19:07 PM
> Subject: Re: [Engine-devel] What type of DB inheritance to use?
> 
> Hi
> 
> From my experience, single table inheritance quickly grows into something
> unmanagable, as more and more (child related) fields are added, each line
> has a few relevant attributes, and a long list of NULL values.
> I would go with option 3. With materialized views, the costs of joins can be
> eliminated.

+1

> 
> 
> On Sun, May 12, 2013 at 12:53 PM, Yair Zaslavsky < yzaslavs at redhat.com >
> wrote:
> 
> 
> 
> 
> 
> 
> 
> 
> From: "Mike Kolesnik" < mkolesni at redhat.com >
> To: "engine-devel" < engine-devel at ovirt.org >
> Sent: Sunday, May 12, 2013 12:42:14 PM
> Subject: [Engine-devel] What type of DB inheritance to use?
> 
> Hi All,
> 
> I would like to have your opinions on which inheritance type to use in the
> DB.
> We are adding an "external provider" entity to the system which will be able
> to provide various resources (networks, hosts, etc).
> 
> These providers will be distinguishable by "type".
> The basic definition of a provider contains:
> 
> 
>     * name
>     * description
>     * url
>     * type
> Some providers might need additional properties such as:
> 
> 
>     * user
>     * password
> 
> In Java this is easily represented by inheritance.
> 
> In the DB however, there are 3 approaches that we can take:
> 
> 
>     1. No inheritance. This means that each type will wit in his own table,
>     with no relation or re-use.
>     2. Single table inheritance. All types sit in a single table, and each
>     has his corresponding columns.
> You forgot to mention discriminator column at option 2 (how are you going to
> differ between sub types) which should be indexed.
> 
> 
> 
> 
> 
>     1.
>     2. Multiple table inheritance. Each type sists in his own table, where
>     the PK is FK for the most basic table (providers).
> 
> Pros for each approach:
> 
> 
>     1. None that I can think of.
>     2. No joins: Better performance Easier for developer to see the DB info
>     Facilitate column reuse
>     3. Constraints can be set on each column
> Cons for each approach:
> 
> 
>     1. No reuse of DB entities + no compliance for column types Most
>     cumbersome to query all providers
>     2. Can't put some constraints on non-base columns (esp. not null)
>     3. Joins are needed - opposite of the pros of 2
> 
> 
> 
> 
> 
>     1.
> 
> From personal experience, I find #2 to be better and easier to work with &
> maintain.
> I think it really depends on the use-case, but I also had better experience
> with 2.
> 
> 
> 
> 
> 
> What are your thoughts?
> 
> Regards,
> Mike
> 
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
> 
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
> 
> 
> 
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>