<div dir="ltr">Hi<div><br></div><div style>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.</div>
<div style>I would go with option 3. With materialized views, the costs of joins can be eliminated.</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sun, May 12, 2013 at 12:53 PM, Yair Zaslavsky <span dir="ltr"><<a href="mailto:yzaslavs@redhat.com" target="_blank">yzaslavs@redhat.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-size:12pt;font-family:times new roman,new york,times,serif"><br><div><br></div><hr><blockquote style="padding-left:5px;font-size:12pt;font-style:normal;margin-left:5px;font-family:Helvetica,Arial,sans-serif;text-decoration:none;font-weight:normal;border-left:2px solid #1010ff">
<b>From: </b>"Mike Kolesnik" <<a href="mailto:mkolesni@redhat.com" target="_blank">mkolesni@redhat.com</a>><br><b>To: </b>"engine-devel" <<a href="mailto:engine-devel@ovirt.org" target="_blank">engine-devel@ovirt.org</a>><br>
<b>Sent: </b>Sunday, May 12, 2013 12:42:14 PM<br><b>Subject: </b>[Engine-devel] What type of DB inheritance to use?<br><div><br></div><div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div>Hi All,<br>
</div><div><br></div><div>I would like to have your opinions on which inheritance type to use in the DB.<br></div><div>We are adding an "external provider" entity to the system which will be able to provide various resources (networks, hosts, etc).<br>
</div><div><br></div><div>These providers will be distinguishable by "type".<br></div><div>The basic definition of a provider contains:</div><div><ul><li> name</li><li>description</li><li>url</li><li>type</li></ul>
<div>Some providers might need additional properties such as:<br></div><div><ul><li>user<br></li><li>password<br></li></ul><div>In Java this is easily represented by inheritance.<br></div><div><br></div><div>In the DB however, there are 3 approaches that we can take:<br>
</div><div><ol><li>No inheritance.<br>This means that each type will wit in his own table, with no relation or re-use.<br></li><li>Single table inheritance.<br>All types sit in a single table, and each has his corresponding columns.</li>
</ol></div></div></div></div></blockquote><div>You forgot to mention discriminator column at option 2 (how are you going to differ between sub types) which should be indexed.</div><blockquote style="padding-left:5px;font-size:12pt;font-style:normal;margin-left:5px;font-family:Helvetica,Arial,sans-serif;text-decoration:none;font-weight:normal;border-left:2px solid #1010ff">
<div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div><div><div><ol><li><br></li><li>Multiple table inheritance.<br>Each type sists in his own table, where the PK is FK for the most basic table (providers).<br>
</li></ol><div><br></div><div>Pros for each approach:<br></div><div><ol><li>None that I can think of.<br></li><li>No joins:<br> Better performance<br> Easier for developer to see the DB info<br> Facilitate column reuse<br>
</li><li>Constraints can be set on each column<br></li></ol></div></div></div></div><div>Cons for each approach:<br></div><div><ol><li>No reuse of DB entities + no compliance for column types<br>Most cumbersome to query all providers<br>
</li><li>Can't put some constraints on non-base columns (esp. not null)<br></li><li>Joins are needed - opposite of the pros of 2</li></ol></div></div></blockquote><blockquote style="padding-left:5px;font-size:12pt;font-style:normal;margin-left:5px;font-family:Helvetica,Arial,sans-serif;text-decoration:none;font-weight:normal;border-left:2px solid #1010ff">
<div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div><ol><li><br></li></ol><div>From personal experience, I find #2 to be better and easier to work with & maintain.</div></div></div></blockquote>
<div>I think it really depends on the use-case, but I also had better experience with 2.</div><blockquote style="padding-left:5px;font-size:12pt;font-style:normal;margin-left:5px;font-family:Helvetica,Arial,sans-serif;text-decoration:none;font-weight:normal;border-left:2px solid #1010ff">
<div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div><div><br></div><div><br></div><div>What are your thoughts?<br></div></div><div><br></div><div>Regards,<br>Mike<br></div><div><br></div></div>
<br>_______________________________________________<br>Engine-devel mailing list<br><a href="mailto:Engine-devel@ovirt.org" target="_blank">Engine-devel@ovirt.org</a><br><a href="http://lists.ovirt.org/mailman/listinfo/engine-devel" target="_blank">http://lists.ovirt.org/mailman/listinfo/engine-devel</a><br>
</blockquote><br></div></div><br>_______________________________________________<br>
Engine-devel mailing list<br>
<a href="mailto:Engine-devel@ovirt.org">Engine-devel@ovirt.org</a><br>
<a href="http://lists.ovirt.org/mailman/listinfo/engine-devel" target="_blank">http://lists.ovirt.org/mailman/listinfo/engine-devel</a><br>
<br></blockquote></div><br></div>