Implementing inheritance in SQL
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:
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!
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?
精彩评论