Re: Suggestions on how to structure database

You will have no problem at all with having MySQL search for only records that are in a certain category/keyword with hundreds of records. You would have no problem with *MILLIONS* of records, actually. You may want to consider breaking some things out into separate tables, however. For example, I would have a table of keywords, and another table to relate keywords to pictures, and then no keyword field in the pictures table: TABLE Keywords KeywordID Keyword 1 Women 2 Men 3 Scenery . . . Pictures PictureID Description ... 1 Pamela Anderson on the beach . . . KeywordPictures KeywordID PictureID 1 1 3 1 . . . Similarly, you can do this with Categories and Pictures, and even Categories with Sub-Categories. You could even make it that the most commonly-requested photos filter "up" to be displayed at "higher" categories in the hierarchy. Also look into the "limit" clause in the MySQL docs to see how to show only the first 10 or 20 thumbnails in a set. ----- Original Message ----- From: Todd Eddy <vrillusions@mail.com> Newsgroups: php.general Sent: Wednesday, November 29, 2000 8:03 PM Subject: [PHP] Suggestions on how to structure database > I have been trying to think this out how to structure my db for this > picture gallery script I am making for the past week and can't really > think of anything that sounded really good, so I thought I would run it > by the list and see what I got. > > The question is I am trying to figure out how to display the pictures > into sections, I'll go into more detail below. > > First off, let me just give you some background on info on me, I have > been working on my webpage using basic html for the 6 years or so, and > SSI for the past couple years. I have also been messing around with CGI > for about a year. This picture gallery I am making is my first PHP > script. The backend is MySQL and cosists of two tables: > > The Main One: > id|keywords|count|pictureURL|thumbnailURL|description|sourceURL|sourceName|g allery|date > > You should be able to understand what most of those are for, but just to > get some things straight the keywords will be used when searching for > pictures, which I will add later, the count increments everytime the > detail page, that shows the full sized picture (pictureURL), > 'description,' and, if I knew where the picture came from, the > 'sourceURL' and the 'sourceName' will be used for that. The gallery is > the main section the picture is a part of. And the date I only update > the date if I change something (not just the counter) > > The Gallery One: > gallery|title|count|description|hidden|date > > the gallery corresponds to the gallery on the main one, so if I have the > picture point to gallery 1, then it would belong to gallery 1 in the > gallery table. The hidden one will be used if I didn't want to show a > certain section, for one reason or another. > > Sorry for the lengthy intro, but wanted to make sure I got everything > accross. I have the detail page up at > http://www.vrillusions.com/picarchive/?cmd=detail&id=1 to > give you an > idea. > > What I can't figure out is how to display all the thumbnails. One of > the ideas I thought of is having the 'title' in the gallery say > something like these: > People - Celebrities - Pamela Anderson > People - Celebrities - Misc > People - Internet - Bill Gates > Places - USA - Ohio - Cleveland > Places - USA - Florida - Miami > Places - France - Paris > ... > > So if I started the table with gallery 1, if I had a picture linked to > gallery 4, that would mean I want it in "Places - USA - Ohio - > Cleveland." > > What would happen when you went to the main page, it would just show > "People" and "Places." and if the person clicked on "People", it > would > then show "Celebrities" and "Internet" and this would keep going until > they got to the last section. and would then show the thumbnails. > > The only concern I have with the above idea is that when the database > gets larger, like several 100 pictures or more, this will take a long > time to search through everytime to get ones that match People, then > People - Celebrities, etc. Although, it shouldn't take really long > since I might only have 50 entries in that gallery table that it would > have to search through to see what sub categories are in a certain > category. > > But I am still faced with the problem of showing all the images in the > main table that would belong to gallery 4, or whatever it happens to > be. I haven't had that much experience with MySQL commands, so they may > be a search function builtin that I can search for gallery 4 in the main > table and just show those. I know it works with the ID, which is used > to get all the data for the "detail" page, because its the "primary key" > or whatever its called, but not sure if it will work with other names > though. > > Like I said, this is all really new to me, just wanted to get some input > as to how well this idea will work, or if this is much too complicated > and theres some easier way to do this. I really wanted to keep the main > table the way it is now since I already made that one page and the admin > page for it already. But that gallery one I can change any way I want. > > Thank you in advance to any replies I get > > -- > Todd Eddy > vrillusions@mail.com > http://www.vrillusions.com/ > > I acceept PGP secured mail, You can get my public key at: > http://www.vrillusions.com/contact/ > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >