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

From: "Liran Zelkha" <liran.zelkha(a)gmail.com&gt; To: "Yair Zaslavsky" <yzaslavs(a)redhat.com&gt; Cc: "engine-devel" <engine-devel(a)ovirt.org&gt; 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.

On Sun, May 12, 2013 at 12:53 PM, Yair Zaslavsky < yzaslavs(a)redhat.com > wrote: From: "Mike Kolesnik" < mkolesni(a)redhat.com > To: "engine-devel" < engine-devel(a)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(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel