Specific entities eg : Librairy documents

[El_Pablo]

Hi,

What would be the best pratice for specialization type?

Here's an example :

In a librairy a member can borrow some documents. The documents are not only books but could be DVDs, CDs, magazines, books...

Each of these specialized documents have their own field, but also share some fields. Books have ISBN, DVDs a serial number, magazines (volume, edition, ...), but all of them have a title.

What would be the best way to design the tables?

A document table with all the fields in it.

OR a table for each specialization link to a generic "document" table.

What's your point of view in this case?

[mr_vodka]

I dont really see the benefit here of seperating them out to individual tables. Just use one table with a type field. Make sure that you use an auto generated serial field for the "real" unique priamry key. You can also create and auxillary field that combines the type with the number field if you wish (ISBN, volume, product serial, etc) with a concatenated calc field.

[El_Pablo]

Well in OOP. Separation facilitate the maintenance of the classes. So if I want to add let say an equipment class I just have to add the class with it's own type.

Adding a new class to a table should ask a lot of maintenance.

But I'm usure in DB development if I can do the same.

[mr_vodka]

Well you can create a second table with your document types. Each type would have its own unique ID. Then a foreign key would be used in the main table that stores the ID of each type of document.

So yes. In relational databases, ideally you should store the different document types in its own table, but in your case it may not be needed as it seems as though there are only a *few different types.

If you do plan on having a bunch of them or plan on changing the wording regularly, then by all means create a secondary table to store the types. Otherwise, you can keep it all in one.

[El_Pablo]

Where the foreign keys should go? In the document table or in the documentType table?