Database schema for "digital archive"

[P.G.]

Hi all,

I'm new to these forums (although I've read them quite a bit). I've been trying to put together an appropriate data schema for a new database I am creating, I thought this would be a good place to ask you guys for advice.

Basically it will be a library of digitized "stuff", from audio recordings to scanned old photographs and documents, to old VHS tapes.

The basic functionality should be for the end-user to type a word, say "hat", and have a list of results returned that all contain "hat" as a keyword. The search results should be categorized ("video", "audio", etc..).

1. What should the database schema look like? I've though about creating a general "Items" table, which would have two foreign keys fk_ID and fk_type, + fields for all the general info.

Then create a separate table for each item type with a global field g_type to identify it, an ID field pk_ID for the objects, + extra fields that are specific to that item type.

Does this sound like a good start?

2. Is it possible to make the Items layout conditional, so that it displays different info according to the different item types?

3. Won't I be running into problems with searching if I want to search through the fields that are specific to an item type? (as opposed to fields from the general Items table)

4. This is a different problem, but how should I handle the keywords? Can I put those in a separate table?

Thanks for your help,

Also, if you have any good general resources for designing this kind of database it would be much appreciated.

Peter