开发者

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:

Implementing inheritance in SQL

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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜