Efficient database setup - experienced users only please
OK folks...please ignore the previous thread. I'll get that deleted.
I'd like to query you on how to most effectively set up a database for a project I'm working on. I'm in the process of cataloguing 35mm slides for my company. Right now we are in the organizing portion of the project. We've got about 1000-1500+ slides that I need to sort can catalog.
The set we have decided on goes something like this:
Sort by Alpha first: A, B, C, etc.
Then sort by subject: Belle Meade Plantation, Belmont Mansion, etc.
then by category: Interior, Exterior, etc.
then by property: daytime, nighttime, trees, etc.
then assign a name: 0001, 0002, etc.
Then we'll take the letter of the alphabet, the first four letters in each category/type and then the number. So using the above as an example, the file name would be A-bell-exte-nigh-0001.
The reason I'm posting here however is that I also want to keep track of these using a database. I will be scanning in these slides for archival purposes and so that we can then use them in our publications.
The question I have is how should I set up the table structure. Starting with the file number (at the end), I need to make sure that each file name/type and category falls under the preceding one for ease of navigation. So, using the above file name the "path" to that file would like this:
Belle Meade Plantation
Nightime would list all files that fall under all of the previous categories/types and so on.
Each final slide will need to have a unique database ID but it also needs to contain the following information:
name (this would our name (A-bell-exte-nigh-0001)).
keywords (we will be using this database to search for slides on our website. One reason it needs to be highly efficient)
My first thoughts are to have a TABLE for each letter , then each subject, then each category, then property but after creating a TABLE for each letter in Access it just looked wrong. I'm using Access since this project is being built using Cold Fusion. We have the option of porting to SQL Server if the need for this gets too big for Access to handle.
Is there anything that I'm leaving out? What should I do, how should I se tthis up?