Implementing inheritance in SQL

Question

I'm having trouble with a class project consisting, among other things, of modeling (using merise) a database for an app-store like. I've come to the point where I have an entity-relationship model that really suits me and seem to fit the constraints of the subject.

On this modeling, I'm using inheritance to mark the difference between and app, and an add-on, both of which have common characteristics. In fact, they have the perfect same characteristics, but they are subject to different associations.

Here is a screenshot that might help you understand:

enter image description here

I was hoping to make Software a view, and App and AddOn tables, supposing the latter two won't have entries with the same id. But I don't know how to do that, and maybe this isn't the right way to do it, so I'm open to suggestions.

I hope I was clear enough, if not, don't hesitate to ask me to be more precise.

Thanks for reading!

Are you forced to use MySQL? PostgreSQL supports table inheritance — user330315
– user330315, Commented Mar 21, 2011 at 7:51
Yes I am. We're already lucky enough to be the first prom that doesn't have to use Oracle — ksol
– ksol, Commented Mar 21, 2011 at 8:14
I wouldn't consider myself lucky if I was forced to use MySQL instead of Oracle - but that's my personal opinion (and totally off-topic) — user330315
– user330315, Commented Mar 21, 2011 at 8:17
I say "lucky" because last semester, the development server was not usable for the better part of the three weeks before the project deadline. At least with MySQL, if this happens again, we can use our own computers. But I agree, this is not an Oracle-related problem, more of a sysadmin, and off-topic in both cases, sorry :-° — ksol
– ksol, Commented Mar 21, 2011 at 8:43
My reading of diagrams can hardly be considered perfect. Could you guide me a little? It says that one add-on can extend 0 to N applications, right? It's just that I would expect it to be the other way round, i.e. one application is extended by 0 to N add-ons. But, again, it may just be my poor reading. — Andriy M
– Andriy M, Commented Mar 21, 2011 at 9:49

Ronnis · Accepted Answer · 2011-03-21 11:43:14Z

3

You have already identified the need for different tables for App and Addon, since they are subject to different associations. Good!

I would suggest realizing Software as a base table, because you can't declare referential integrity to a view (categorized as... in your picture). I noticed several other incoming associations as well.

Software(
   software_id
  ,type
  ,name
  ,price
  ,entry_date
  ,description
  ,primary key(software_id)
)

I added a discriminator column type, which should contain a value identifying whether this piece of software is an Addon or an App.

Addon(
   software_id
  ,primary key(software_id)
  ,foreign key(software_id) references software(software_id)
)

App(
   software_id
  ,primary key(software_id)
  ,foreign key(software_id) references software(software_id)
)

Note that the primary keys in Addon and App refers to the primary key in software. In a way, you can think of software_id as a kind of "data type". The column software_id in Addon doesn't just contain any random unique number. The value must come from one of those numbers that identifies a piece of software.

Is this the kind of answer you were looking for?

answered Mar 21, 2011 at 11:43

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ksol Over a year ago

>[...] because you can't declare referential integrity to a view [...] Yep. I was hoping I could move the field "category_id" to the "subclasses", though I admit I have no idea if this would have worked. Otherwise, what you're suggesting seems to do the trick, however I wonder: with this solution, inserting data requires two queries, am I wrong ? One to insert in the software view, one to insert in one of the two subclasses.

Ronnis Over a year ago

Yes, it does and it's not a problem as long as you are using transactions (and innodb). You could also use a stored procedure which handles it for your application. I don't know if MySQL supports updatable views. If it does, then you could define an Addon-view which is the join between Software and Addon (including all columns). Inserting 1 row into the view would result in two inserts into the respective tables behind the scenes. But as I said, I don't know if it works in MySQL.

ksol Over a year ago

I'm gonna try this this afternoon, but from where I stand, combining your solution with a trigger should do the trick. Also, from what I've just read, it isn't possible to perform insert on multiple tables from a view. Thanks for your time !

ksol Over a year ago

So here I am, with the same table schemes, except AddOn, which have a id_app field, reflecting the app extended by said addon. There is no way that I can supply this field when I'm doing an insertion on Software, am I wrong ?

Ronnis Over a year ago

@katen, not sure I understand what you mean? In the trigger? You need two code paths, one for each sub type (addon, app) either in the database (stored procedure) or in your application.

|

Collectives™ on Stack Overflow

Implementing inheritance in SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related