Efficient database setup - experienced users only please

[creole]

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:

B
  Belle Meade Plantation
    Exterior
      Nighttime
        0001

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:

id
name (this would our name (A-bell-exte-nigh-0001)).
date entered
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?

[aspen]

Initially it looks like you going at this the wrong way.

You don't need a table for letters, if you ever want to select just the B's or what have you can you do...

select * from table where field like 'B%'

The most normal structure would be like this:

main
-----
key
name
date
SubID
CatID
PropID

Then you'd have a table for all the subjects

subject_Table
-----
key
Subject Name
Anything Else

One for the Categories

cat_Table
------
key
Category Name
anything else

one for the properties

prop_table
----
key
Property Name
anything else

I need to make sure that each file name/type and category falls under the preceding one for ease of navigation.

It doesn't matter what order things are in in the DB, you can spit them out on the page in any order or in parts or however you want to do it.

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.

Do you have to do that? There really isn't a reason to with the database laid out above. Unless thats something you're already doing and you need to use it for some reason.

For keywords... you could already search on the fields in the database, name, category, property, etc. If that wasn't enough then you could do this two ways.

You could include another field at the end of the table as a text field and put a comma delineated list in it. This isn't the most normal way to do it but it might be the easiest in your situation.

The most normal way would be to make two more tables.

keywords
-----
id
word

keyword_slide_relation_table
------
keywordID
SlideID

And handle keywords that way. The above is I believe how this forum handles searches. Its more complicated, it'll be up to you to decide if you want to go to that level of normalization.

[CrazyCrane]

You seem to be viewing your database structure like a directory structure, which is almost certainly not the most efficient way of storing the information!

From the looks of it, you only really need one table. You just have a collection of slides, about which you need to store some information. So, simply have a single table which has fields for name, ID, date entered etc., and also a field for each of the categories - alpha first, subject, category, property, slide name.

By doing this, when you come to write your CFML and want to display a section you can easily create SQL queries to get exactly the data you want. Say you want all the "A"'s in "Belle Meade Plantation" (so in your diagram above you're at the second level of the navigation tree), you'd simply write a query with a where clause along the lines of "WHERE alpha_first = 'A' AND subject = 'Belle Meade Plantation'" - would bring back all of the files in that category.

If instead you wanted to bring back further categories (such as interior, exterior etc.) you could easily select DISTINCT categories with that where clause etc. This means you can easily build your navigation tree dynamically from the database.

I'm not sure that made much sense. But as far as storing the data goes, you only have one logical kind of object, which are slides. The work of sorting them, and selecting only the records you want, is handled by your CFML code not your database design (in this case). If you're worried about speed you could index the fields you'll be sorting on most often.

Hope that helps

[Kevin Yank]

creole PM'd me asking me to chime in on this thread, but after reading aspen's post I have very little else to say except "I agree with what he said!"

[aspen]

Oh now I do feel special.

[Kevin Yank]

Do you mock me, sir?

[aspen]

Not at all.

A compliment from world renowned technical author Kevin Yank makes my day.

[Jeremy W.]

So, you're storing pictures and so on? Why not use a BLOB for the actual data? This would skip the complicated naming of files or pointing to directories which is all so inefficient.

Beyond that, the db structure already outlined looks great to me.

[seanf]

Storing images in a table is much more inefficient

Sean

[Jeremy W.]

Then trying to individually name, store, archive 1500 images? It may be slightly (hardly at all) more server intensive (depending on DBMS) but it is definitely worth thinking about.

[hillsy]

I'm not really in favour of storing the image data in a database. For reasons of efficiency and ease of update I guess. At work our graphic designers like to be able to easily retrieve and save images to a directory.

This could also be a hang-over from the fact that I develop a lot in Access where storing such things gives nasty surprises when it comes to file size (and stability)

BTW this is one of my only gripes with Zope - it locks everything away in a database where you can't get at it except programatically. Better hope your program keeps working

[Jeremy W.]

I'm talking about things like Oracle and SQL Server (dunno if MySQL has BLOB or CLOB). Anyways, just my piece of pie, I won't be offended if you don't take my suggestion (since everyone ELSE in this discussion is much more "godlike" then me )

[seanf]

MySQL has BLOB

Sean

[creole]

OK...

Let me chime in here. The process will work like so:

1) Organize slides

2) Set up the naming system. This is important because each slide needs to have a unique name. This name needs to appear on a label on each slide. The reason is that we frequently send these slides out to our clients. We need to know where the slide goes when it is returned to us.

3) Scan slides, number them, batch them into their three sizes (thumb, lo-res[web], hi-res[print]).

4) Once we have enough slides to fill a CD, it would get burned. The database would need to store not only the location of the CD that the digital image is on, but also the location where the hard copy is.

So:

-= yes the slide does need to have a "name" on it. We decided on that so that it would be easily recognizable.

-= I'm not sure how the keyword system would work. I don't see how associating the slide id with the keyword id would allow me to find all slides of Belle Meade Plantation.

[aspen]

Its a many to many relationship, and in those kinds of relationships you need to make a table for them.

Lets say you got a slide(Slide 1) with keywords tree house and bench, and one(Slide 2) with keywords car fence and tree

Your tables would be like this

keywords
--------
id|keyword
1|bench
2|tree
3|car
4|house
5|fence

relationship_table
SlideID|KeywordID
1|1
1|4
1|2
2|3
2|5
2|2

The point of database normalization is to get rid of duplicate data. Which is why for big systems you use things like this for searching. With your only 1500 records you may or may not do it, its up to you.

But to find all the slides with tree you would:

select SlideID from relationship_table where keywords.keyword = "$YourKeyWord" and keywords.id = relationship_table.KeywordID

Basically.

Of course if you just put a comma dileanated list in a text field it'd be

select * from main where keyword like "%$YourKeyWord%"

One is obviously less complex, the other is technically more correct/efficient