SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    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?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Nick Wilson [ - email - ]

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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!"
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Oh now I do feel special.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Do you mock me, sir?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not at all.

    A compliment from world renowned technical author Kevin Yank makes my day.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Storing images in a table is much more inefficient

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  10. #10
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  11. #11
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  12. #12
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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 )
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  13. #13
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL has BLOB

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  14. #14
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  15. #15
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •