advise on how to set mysql tables out for my own gallery

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
farkewie
Forum Newbie
Posts: 22
Joined: Sat Jun 02, 2007 11:25 pm

advise on how to set mysql tables out for my own gallery

Post by farkewie »

I am creating my own image gallery, at this point i dont want any comments or users gallerys but i want to leave it expandable in for for the futre
thing is i have no idea what my database should look like?
i am storing the image paths and thumb paths in there at the momet but i think it could be a lot better.

at the moment i have only 1 tabe and it has

id, albumname, imagepath, thumbpath, date added

any advise to get me started is welcome.[/syntax]
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

Hi,

One way of doing would be to normalise your tables.

I would probably create the following tables to accomplish what you're trying to do:

tblAlbum(album_id, album_name)
tblImage(image_id, image_name, image_filename)
tblAlbumImage(album_id, image_id, date_added)

The three tables above can now be used to store all of your images in many different albums.

With regard to your thumbs etc... simply create directories for each different type of image i.e. images/thumbs or images/hi-res etc... this way you will be able to reference/use one filename (stored in tblImage) and where necessary use a different directory path for thumb or hi-res (obviously the actual image file will be stored in these directories).

Hope this helps.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: advise on how to set mysql tables out for my own gallery

Post by califdon »

farkewie wrote:I am creating my own image gallery, at this point i dont want any comments or users gallerys but i want to leave it expandable in for for the futre
thing is i have no idea what my database should look like?
i am storing the image paths and thumb paths in there at the momet but i think it could be a lot better.

at the moment i have only 1 tabe and it has

id, albumname, imagepath, thumbpath, date added

any advise to get me started is welcome.[/syntax]
In anticipation of future users' galleries or comments, you could add columns for username and comments, but really, adding columns to a table is so easy to do later, there's probably no need to include everything from the beginning.

The basic way to design a relational database is this:
Decide what part of the real world you want to model, identify the entities and their properties.

What that means is, decide what it is you want to store data about. In your case, I'd guess that you want to store data about images in some subdirectory of your web server, so that you can display them on a web page. That's the real world that you want to model in your database. If you want to model a broader part of the real world, that's fine, just define it. It really helps to do this on paper, so you can come back and re-read it from time to time, to see if it is still valid.
Once you have a model, what entities are in it? An entity is a person or an object or a place or an event or a transaction, etc. Here, your main entity is almost certainly an image. But maybe another entity would be a user, if you are planning to have users in your model. If you want to include the entity of album, you would need a table for that, as aceconcepts proposed, because the point of thinking in terms of entities is that each entity requires a table of its own. Once you have identified all the entities that you want to model, you then determine what properties or attributes each entity has. These become the fields in the table for that entity.
After that, you consider what relationships exist in the real world between your entities. A relationship is always either one-to-many, many-to-many or one-to-one. To implement many-to-many relationships requires another table that contains the primary keys to the other two tables, as aceconcepts showed you. In MySQL, relationships are implemented with JOINS in SQL statements.
Post Reply