Having a class for each table in the database

Question

I'm creating a software for my friends library. For now I have a table in the database for books and movies, but lets say that the user would want to add cds aswell. Then I'd have to let the user create a table in the database for cds. The design so far, is to be having a class for each table in the database, with its fields, and methods for inserting and updating data in the database (c# and mysql). How would I go ahead and make a "general" class for all tables in the database, and not for each specific like I'm doing now.

I'm looking for a name on a pattern or something like that, not a complete tutorial in reply.

Thanks :)

Answer 1

You can do that using Map or Dictionary. In simple words using name/value pairs. But that is a pretty bad design for what you are doing. This will make things difficult for you and manage as well.

I know what you are thinking about having to write the same methods over and over again for all the tables. For that case you should be using some kind of ORM (Object Relation Mapping). For java you can use Hibernate, Toplink. NHibernate is a port of Hibernate in .Net.

Answer 2

You could use something which is called STI: single table inheritance. It is suited to places like this, i guess, where a number of attributes are shared, and some are seperate.

So you could create a table, call it LibraryItem, and it has a type, which could be CD, book, dvd and a title, and some fields that will be filled in for some type and empty for others.

Normally it is best to create seperate tables for each, but sometimes the STI approach is more flexible. You can easily add a new type: e.g. LP or VHS, as long as it does not introduce too much new attributes/fields.

Answer 3

Here are some patterns:

• Single Table Inheritance (what you think you want to do)
• Class Table Inheritance (what you are doing now)
• Concrete Table Inheritance