<html><body><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.<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.<br></li></ol><div>From personal experience, I find #2 to be better and easier to work with & maintain.<br></div><div><br></div><div>What are your thoughts?<br></div></div><div><br></div><div><span name="x"></span>Regards,<br>Mike<span name="x"></span><br></div><div><br></div></div></body></html>