<html><body><div style="font-family: times new roman, new york, times, serif; font-size: 12pt; color: #000000"><br><div><br></div><hr id="zwchr"><blockquote style="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><b>From: </b>"Mike Kolesnik" <mkolesni@redhat.com><br><b>To: </b>"engine-devel" <engine-devel@ovirt.org><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-family: times new roman, new york, times, serif; font-size: 12pt; color: #000000"><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="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><div style="font-family: times new roman, new york, times, serif; font-size: 12pt; color: #000000"><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="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><div style="font-family: times new roman, new york, times, serif; font-size: 12pt; color: #000000"><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="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><div style="font-family: times new roman, new york, times, serif; font-size: 12pt; color: #000000"><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>Engine-devel@ovirt.org<br>http://lists.ovirt.org/mailman/listinfo/engine-devel<br></blockquote><br></div></body></html>