Hi, I was wondering if someone could look over this simple design I have for a media tracking system. I have mainly the media table and sound/image/video/text tables. So, a person can make a request for a media. (check table). This request would be either - initial, checked out, checked in. (check_request table).

I was wondering if this format is a good design.. Any input would be much appreciated.

Thanks.

Is Media your supertype with Image, Sound, Video, and Text as subtypes?

Is filetype_id the supertype/subtype discriminator?

Is there an alternate key on Media.check_id?

Thank you very much for the reply.

1. I didn't really think about that at first.. I'm still new and learning about databases, modelling and all that. Looking at it now, yes, media would be the supertype. I'm not sure how that would be implemented though. (Something I need to learn..)

2. filetype_id was supposed to be for a reference table of common filetype extensions. I've updated the model to include that. But, since the image/sound/etc tables are subtypes, do they require this discriminator key, or is the primary key (image_id, sound_id) enough?

3. I'm not sure what you mean by that. Are you saying that the key for media is not unique enough, thus requiring an alternate key?

Thanks again.

What I meant by the alternate key on Media.check_id was that I am assuming that since you have a 0/1 to 0/1 cardinality between check and media, you would want to enforce no more than one check per media.