Reputation: 1583

A question about database design

I am planing on a Library Management System where students check out items.

My problem is with how to design the database if the library carry different medias (eg. mircofilm, dvds, cds etc) along with books. My problem lies with the fact that dvds don't have the same attributes that of books, or books don't have the same attributes as microfilms.

So, how should I handle all these different types of medias?

Thank you.

Upvotes: 1

Answers (5)

Walter Mitty

Reputation: 18950

Here is a free data model about Media libraries, from the Database Answers Website.

Upvotes: 1

Shiv Kumar

Reputation: 9799

If the various media have some common attributes then put those in one table and put the other attributes in a separate table each.

Further, this common table should have a mediaType field, which is a foreign key from another table.

For example

Table MediaType

Id  Name
1   Book
2   DVD
3   CD
4   Microfilm

Table Media

Id  MediaType  Title
1   1          Gone With the Wind
2   2          Avatar
3   2          Gladiator
4   2          Never Say Never
5   3          Some Music CD

Table MediaBook

Id MediaId  Author
1  1        Margaret Mitchell

Table MediaDVD

Id  MediaId  Director
1   2        James Cameron

etc.

This way design will also help in any reporting you need to do since your Media table can point to the table in which additional information for the specific type of media exists. This design will also allow you to extend the attributes of various media independently of other types of media as well as easily add new media types to your system. This design will also give you a normalized data model while maintaing the integrity of your data using simple relationships and null/not null fields.

Upvotes: 4

Craig

Reputation: 18734

Would an option not to have a MediaType reference table, holding the types of media you have, and then the main transaction table has ALL possible fields... Then, have check constraints based on the MediaTypeId, checking which fields can and can't be NULL?

I find that the simplest and most maintainable solution.

Upvotes: 2