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

[Yair Zaslavsky]

----- Original Message -----

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