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 <[email protected]>wrote: > > > ------------------------------ > > *From: *"Mike Kolesnik" <[email protected]> > *To: *"engine-devel" <[email protected]> > *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 > [email protected] > http://lists.ovirt.org/mailman/listinfo/engine-devel > > > > _______________________________________________ > Engine-devel mailing list > [email protected] > http://lists.ovirt.org/mailman/listinfo/engine-devel > >
_______________________________________________ Engine-devel mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-devel
