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

[Liran Zelkha]

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.


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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/engine-devel/attachments/20130512/eeff2366/attachment.html>