From mkolesni at redhat.com Sun May 12 05:42:14 2013 Content-Type: multipart/mixed; boundary="===============8069000614769093023==" MIME-Version: 1.0 From: Mike Kolesnik To: devel at ovirt.org Subject: [Engine-devel] What type of DB inheritance to use? Date: Sun, 12 May 2013 05:42:14 -0400 Message-ID: <1682384155.165721.1368351734157.JavaMail.root@redhat.com> In-Reply-To: 478978763.164674.1368350828218.JavaMail.root@redhat.com --===============8069000614769093023== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable ------=3D_Part_165720_1428728257.1368351734157 Content-Type: text/plain; charset=3Dutf-8 Content-Transfer-Encoding: 7bit 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 abl= e 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. = 3. 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 cumber= some 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. = >From personal experience, I find #2 to be better and easier to work with &= maintain. = What are your thoughts? = Regards, = Mike = ------=3D_Part_165720_1428728257.1368351734157 Content-Type: text/html; charset=3Dutf-8 Content-Transfer-Encoding: quoted-printable

Hi All,

= =3D

I would like to have your opinions on which inheritance type to use in= =3D the DB.

We are adding an "external provider" entity to the s= =3D ystem which will be able to provide various resources (networks, hosts, etc= =3D ).

These providers will be distinguishable by = =3D "type".

The basic definition of a provider contains:

name
description
url
type

= =3D Some providers might need additional properties such as:

user
password

In Java this is easily repr= =3D esented by inheritance.

In the DB however, the= =3D re are 3 approaches that we can take:

No inheritance.= =3D
This means that each type will wit in his own table, with no relation o= =3D r re-use.
Single table inheritance.
All types sit in a singl= =3D e table, and each has his corresponding columns.
Multiple table= =3D inheritance.
Each type sists in his own table, where the PK is FK for t= =3D he most basic table (providers).

Pros for = =3D each approach:

None that I can think of.
= =3D No joins:
    Better performance
  &nbs= =3D p; Easier for developer to see the DB info
    = =3D Facilitate column reuse
Constraints can be set on each column

Cons for each approach:

<= =3D div>

No reuse of DB entities + no compliance for column types
Mos= =3D t cumbersome to query all providers
Can't put some constraints = =3D on non-base columns (esp. not null)
Joins are needed - opposite= =3D of the pros of 2.

From personal experience, I find #2 to= =3D be better and easier to work with & maintain.

= =3D

What are your thoughts?

Regards,
Mike

------=3D_Part_165720_1428728257.1368351734157-- --===============8069000614769093023== Content-Type: multipart/alternative MIME-Version: 1.0 Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="attachment.bin" LS0tLS0tPV9QYXJ0XzE2NTcyMF8xNDI4NzI4MjU3LjEzNjgzNTE3MzQxNTcKQ29udGVudC1UeXBl OiB0ZXh0L3BsYWluOyBjaGFyc2V0PXV0Zi04CkNvbnRlbnQtVHJhbnNmZXItRW5jb2Rpbmc6IDdi aXQKCkhpIEFsbCwgCgpJIHdvdWxkIGxpa2UgdG8gaGF2ZSB5b3VyIG9waW5pb25zIG9uIHdoaWNo IGluaGVyaXRhbmNlIHR5cGUgdG8gdXNlIGluIHRoZSBEQi4gCldlIGFyZSBhZGRpbmcgYW4gImV4 dGVybmFsIHByb3ZpZGVyIiBlbnRpdHkgdG8gdGhlIHN5c3RlbSB3aGljaCB3aWxsIGJlIGFibGUg dG8gcHJvdmlkZSB2YXJpb3VzIHJlc291cmNlcyAobmV0d29ya3MsIGhvc3RzLCBldGMpLiAKClRo ZXNlIHByb3ZpZGVycyB3aWxsIGJlIGRpc3Rpbmd1aXNoYWJsZSBieSAidHlwZSIuIApUaGUgYmFz aWMgZGVmaW5pdGlvbiBvZiBhIHByb3ZpZGVyIGNvbnRhaW5zOiAKCgogICAgKiBuYW1lIAogICAg KiBkZXNjcmlwdGlvbiAKICAgICogdXJsIAogICAgKiB0eXBlIAoKU29tZSBwcm92aWRlcnMgbWln aHQgbmVlZCBhZGRpdGlvbmFsIHByb3BlcnRpZXMgc3VjaCBhczogCgoKICAgICogdXNlciAKICAg ICogcGFzc3dvcmQgCgpJbiBKYXZhIHRoaXMgaXMgZWFzaWx5IHJlcHJlc2VudGVkIGJ5IGluaGVy aXRhbmNlLiAKCkluIHRoZSBEQiBob3dldmVyLCB0aGVyZSBhcmUgMyBhcHByb2FjaGVzIHRoYXQg d2UgY2FuIHRha2U6IAoKCiAgICAxLiBObyBpbmhlcml0YW5jZS4gVGhpcyBtZWFucyB0aGF0IGVh Y2ggdHlwZSB3aWxsIHdpdCBpbiBoaXMgb3duIHRhYmxlLCB3aXRoIG5vIHJlbGF0aW9uIG9yIHJl LXVzZS4gCiAgICAyLiBTaW5nbGUgdGFibGUgaW5oZXJpdGFuY2UuIEFsbCB0eXBlcyBzaXQgaW4g YSBzaW5nbGUgdGFibGUsIGFuZCBlYWNoIGhhcyBoaXMgY29ycmVzcG9uZGluZyBjb2x1bW5zLiAK ICAgIDMuIE11bHRpcGxlIHRhYmxlIGluaGVyaXRhbmNlLiBFYWNoIHR5cGUgc2lzdHMgaW4gaGlz IG93biB0YWJsZSwgd2hlcmUgdGhlIFBLIGlzIEZLIGZvciB0aGUgbW9zdCBiYXNpYyB0YWJsZSAo cHJvdmlkZXJzKS4gCgpQcm9zIGZvciBlYWNoIGFwcHJvYWNoOiAKCgogICAgMS4gTm9uZSB0aGF0 IEkgY2FuIHRoaW5rIG9mLiAKICAgIDIuIE5vIGpvaW5zOiBCZXR0ZXIgcGVyZm9ybWFuY2UgRWFz aWVyIGZvciBkZXZlbG9wZXIgdG8gc2VlIHRoZSBEQiBpbmZvIEZhY2lsaXRhdGUgY29sdW1uIHJl dXNlIAogICAgMy4gQ29uc3RyYWludHMgY2FuIGJlIHNldCBvbiBlYWNoIGNvbHVtbiAKQ29ucyBm b3IgZWFjaCBhcHByb2FjaDogCgoKICAgIDEuIE5vIHJldXNlIG9mIERCIGVudGl0aWVzICsgbm8g Y29tcGxpYW5jZSBmb3IgY29sdW1uIHR5cGVzIE1vc3QgY3VtYmVyc29tZSB0byBxdWVyeSBhbGwg cHJvdmlkZXJzIAogICAgMi4gQ2FuJ3QgcHV0IHNvbWUgY29uc3RyYWludHMgb24gbm9uLWJhc2Ug Y29sdW1ucyAoZXNwLiBub3QgbnVsbCkgCiAgICAzLiBKb2lucyBhcmUgbmVlZGVkIC0gb3Bwb3Np dGUgb2YgdGhlIHByb3Mgb2YgMi4gCgo+RnJvbSBwZXJzb25hbCBleHBlcmllbmNlLCBJIGZpbmQg IzIgdG8gYmUgYmV0dGVyIGFuZCBlYXNpZXIgdG8gd29yayB3aXRoICYgbWFpbnRhaW4uIAoKV2hh dCBhcmUgeW91ciB0aG91Z2h0cz8gCgpSZWdhcmRzLCAKTWlrZSAKCgotLS0tLS09X1BhcnRfMTY1 NzIwXzE0Mjg3MjgyNTcuMTM2ODM1MTczNDE1NwpDb250ZW50LVR5cGU6IHRleHQvaHRtbDsgY2hh cnNldD11dGYtOApDb250ZW50LVRyYW5zZmVyLUVuY29kaW5nOiBxdW90ZWQtcHJpbnRhYmxlCgo8 aHRtbD48Ym9keT48ZGl2IHN0eWxlPTNEImZvbnQtZmFtaWx5OiB0aW1lcyBuZXcgcm9tYW4sIG5l dyB5b3JrLCB0aW1lcywgc2U9CnJpZjsgZm9udC1zaXplOiAxMnB0OyBjb2xvcjogIzAwMDAwMCI+ PGRpdj5IaSBBbGwsPGJyPjwvZGl2PjxkaXY+PGJyPjwvZGl2Pj0KPGRpdj5JIHdvdWxkIGxpa2Ug dG8gaGF2ZSB5b3VyIG9waW5pb25zIG9uIHdoaWNoIGluaGVyaXRhbmNlIHR5cGUgdG8gdXNlIGlu PQogdGhlIERCLjxicj48L2Rpdj48ZGl2PldlIGFyZSBhZGRpbmcgYW4gImV4dGVybmFsIHByb3Zp ZGVyIiBlbnRpdHkgdG8gdGhlIHM9CnlzdGVtIHdoaWNoIHdpbGwgYmUgYWJsZSB0byBwcm92aWRl IHZhcmlvdXMgcmVzb3VyY2VzIChuZXR3b3JrcywgaG9zdHMsIGV0Yz0KKS48YnI+PC9kaXY+PGRp dj48YnI+PC9kaXY+PGRpdj5UaGVzZSBwcm92aWRlcnMgd2lsbCBiZSBkaXN0aW5ndWlzaGFibGUg YnkgPQoidHlwZSIuPGJyPjwvZGl2PjxkaXY+VGhlIGJhc2ljIGRlZmluaXRpb24gb2YgYSBwcm92 aWRlciBjb250YWluczo8L2Rpdj48ZGk9CnY+PHVsPjxsaT4gbmFtZTwvbGk+PGxpPmRlc2NyaXB0 aW9uPC9saT48bGk+dXJsPC9saT48bGk+dHlwZTwvbGk+PC91bD48ZGl2Pj0KU29tZSBwcm92aWRl cnMgbWlnaHQgbmVlZCBhZGRpdGlvbmFsIHByb3BlcnRpZXMgc3VjaCBhczo8YnI+PC9kaXY+PGRp dj48dWw+PQo8bGk+dXNlcjxicj48L2xpPjxsaT5wYXNzd29yZDxicj48L2xpPjwvdWw+PGRpdj5J biBKYXZhIHRoaXMgaXMgZWFzaWx5IHJlcHI9CmVzZW50ZWQgYnkgaW5oZXJpdGFuY2UuPGJyPjwv ZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+SW4gdGhlIERCIGhvd2V2ZXIsIHRoZT0KcmUgYXJlIDMg YXBwcm9hY2hlcyB0aGF0IHdlIGNhbiB0YWtlOjxicj48L2Rpdj48ZGl2PjxvbD48bGk+Tm8gaW5o ZXJpdGFuY2UuPQo8YnI+VGhpcyBtZWFucyB0aGF0IGVhY2ggdHlwZSB3aWxsIHdpdCBpbiBoaXMg b3duIHRhYmxlLCB3aXRoIG5vIHJlbGF0aW9uIG89CnIgcmUtdXNlLjxicj48L2xpPjxsaT5TaW5n bGUgdGFibGUgaW5oZXJpdGFuY2UuPGJyPkFsbCB0eXBlcyBzaXQgaW4gYSBzaW5nbD0KZSB0YWJs ZSwgYW5kIGVhY2ggaGFzIGhpcyBjb3JyZXNwb25kaW5nIGNvbHVtbnMuPGJyPjwvbGk+PGxpPk11 bHRpcGxlIHRhYmxlPQogaW5oZXJpdGFuY2UuPGJyPkVhY2ggdHlwZSBzaXN0cyBpbiBoaXMgb3du IHRhYmxlLCB3aGVyZSB0aGUgUEsgaXMgRksgZm9yIHQ9CmhlIG1vc3QgYmFzaWMgdGFibGUgKHBy b3ZpZGVycykuPGJyPjwvbGk+PC9vbD48ZGl2Pjxicj48L2Rpdj48ZGl2PlByb3MgZm9yID0KZWFj aCBhcHByb2FjaDo8YnI+PC9kaXY+PGRpdj48b2w+PGxpPk5vbmUgdGhhdCBJIGNhbiB0aGluayBv Zi48YnI+PC9saT48bGk+PQpObyBqb2luczo8YnI+Jm5ic3A7Jm5ic3A7Jm5ic3A7Jm5ic3A7QmV0 dGVyIHBlcmZvcm1hbmNlPGJyPiZuYnNwOyZuYnNwOyZuYnM9CnA7Jm5ic3A7RWFzaWVyIGZvciBk ZXZlbG9wZXIgdG8gc2VlIHRoZSBEQiBpbmZvPGJyPiZuYnNwOyZuYnNwOyZuYnNwOyZuYnNwOz0K RmFjaWxpdGF0ZSBjb2x1bW4gcmV1c2U8YnI+PC9saT48bGk+Q29uc3RyYWludHMgY2FuIGJlIHNl dCBvbiBlYWNoIGNvbHVtbjxiPQpyPjwvbGk+PC9vbD48L2Rpdj48L2Rpdj48L2Rpdj48L2Rpdj48 ZGl2PkNvbnMgZm9yIGVhY2ggYXBwcm9hY2g6PGJyPjwvZGl2Pjw9CmRpdj48b2w+PGxpPk5vIHJl dXNlIG9mIERCIGVudGl0aWVzICsgbm8gY29tcGxpYW5jZSBmb3IgY29sdW1uIHR5cGVzPGJyPk1v cz0KdCBjdW1iZXJzb21lIHRvIHF1ZXJ5IGFsbCBwcm92aWRlcnM8YnI+PC9saT48bGk+Q2FuJ3Qg cHV0IHNvbWUgY29uc3RyYWludHMgPQpvbiBub24tYmFzZSBjb2x1bW5zIChlc3AuIG5vdCBudWxs KTxicj48L2xpPjxsaT5Kb2lucyBhcmUgbmVlZGVkIC0gb3Bwb3NpdGU9CiBvZiB0aGUgcHJvcyBv ZiAyLjxicj48L2xpPjwvb2w+PGRpdj5Gcm9tIHBlcnNvbmFsIGV4cGVyaWVuY2UsIEkgZmluZCAj MiB0bz0KIGJlIGJldHRlciBhbmQgZWFzaWVyIHRvIHdvcmsgd2l0aCAmYW1wOyBtYWludGFpbi48 YnI+PC9kaXY+PGRpdj48YnI+PC9kaXY+PQo8ZGl2PldoYXQgYXJlIHlvdXIgdGhvdWdodHM/PGJy PjwvZGl2PjwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+PHNwYW4gbmFtZT0KPTNEIngiPjwvc3Bh bj5SZWdhcmRzLDxicj5NaWtlPHNwYW4gbmFtZT0zRCJ4Ij48L3NwYW4+PGJyPjwvZGl2PjxkaXY+ PGJyPjwvPQpkaXY+PC9kaXY+PC9ib2R5PjwvaHRtbD4KLS0tLS0tPV9QYXJ0XzE2NTcyMF8xNDI4 NzI4MjU3LjEzNjgzNTE3MzQxNTctLQo= --===============8069000614769093023==--