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

[Itamar Heim]

On 05/12/2013 12:42 PM, Mike Kolesnik wrote:
> 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

what type of provider won't require authentication?

>
> 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.
>  3. 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.
>
>  From personal experience, I find #2 to be better and easier to work
> with & maintain.
>
> What are your thoughts?
>
> Regards,
> Mike
>
>
>
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>