4:53 a.m.
------=_Part_119211_1477841530.1368352381010
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
----- Original Message -----
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
------=_Part_119211_1477841530.1368352381010
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><body><div style=3D"font-family: times new roman, new york,
times, se=
rif; font-size: 12pt; color:
#000000"><br><div><br></div><hr
id=3D"zwchr"><=
blockquote style=3D"border-left:2px solid #1010FF;margin-left:5px;padding-l=
eft:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:non=
e;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><b>From:
</b>"Mik=
e Kolesnik" &lt;mkolesni(a)redhat.com&gt;<br><b>To:
</b>"engine-devel" <en=
gine-devel(a)ovirt.org&gt;<br><b>Sent: </b>Sunday, May 12, 2013
12:42:14 PM<b=
r><b>Subject: </b>[Engine-devel] What type of DB inheritance to
use?<br><di=
v><br></div><div style=3D"font-family: times new roman, new york,
times, se=
rif; 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 s=
ystem 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><di=
v><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 repr=
esented by
inheritance.<br></div><div><br></div><div>In the DB
however, the=
re 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 o=
r re-use.<br></li><li>Single table inheritance.<br>All types sit
in a singl=
e table, and each has his corresponding
columns.</li></ol></div></div></div=
</div></blockquote><div>You forgot to mention
discriminator column a=
t option 2 (how are you going to differ between
sub types) which should be =
indexed.</div><blockquote style=3D"border-left:2px solid
#1010FF;margin-lef=
t:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text=
-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><d=
iv style=3D"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 th=
e most basic table
(providers).<br></li></ol><div><br></div><div>Pros
for e=
ach approach:<br></div><div><ol><li>None that I can think
of.<br></li><li>N=
o joins:<br> Better
performance<br>  =
; Easier for developer to see the DB
info<br> F=
acilitate 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><d=
iv><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 o=
n 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=3D"bor=
der-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=3D"font-family: times new
rom=
an, 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 easi=
er 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=3D"border-left:2px solid
#1010FF;margin-left:5px;pa=
dding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decorat=
ion:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><div style=
=3D"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 thoug=
hts?<br></div></div><div><br></div><div>Regards,<br>Mike<br></div><div><br>=
</div></div><br>_______________________________________________<br>Engine-d=
evel mailing list<br>Engine-devel@ovirt.org<br>http://lists.ovirt.org/mailm=
an/listinfo/engine-devel<br></blockquote><br></div></body></html>
------=_Part_119211_1477841530.1368352381010--