I’m delving into my first SQL database (MySQL), and was wondering if any of you had the time to help point me in the right direction.
The database is going to be used to store images in an organized fashion (can you put images in an SQL database?), and the information I want to store in the database is as follows:
Picture Title
Picture Number (unique identifier for each picture)
Category
Potential Second Category
The Image Itself
Like I said, this is my first real database. I’m not sure what the best way to organize this information is. I’d like to be able to call up both individual images based on Title or Number, as well as pull up categories of images.
First off you’ll need 2 tables - Images and Categories.
Categories should have an ID (a unique ID) and a name. Also maybe a column for SortOrder (a number) if you plan on sorting your categories in any particular way other than by name.
Your images table should have ID (unique), Title, image file name (you can’t store an actual image in MySQL), and then a field (or 2) for category ID. Reference the category by ID in the category field(s) instead of by name.
Another option, instead of having 2 category fields in the images table, that would let you put images into as many categories as you want, is to have a 3rd table called CategoriesImages. Each row in this table would be a categoryID and an imageID. So if you wanted to put ImageID 12 into categories 1, 2, 3 and 4 the data would look like this:
1 12
2 12
3 12
4 12
That would make your app much more expandable in the long run.
If not, then just use the 2 category fields in the Images table.
Again, this may be a basic question, but I had the vague notion having read things and having worked a tiny bit in Access for work, that I might want to have the categories be in their own table, and then ID them by number in the table for the images.
What I’m not totally sure of is why I have this vague notion that that would be a good idea. I could then… I’m not sure. What does it allow me to do that simply having the categories themselves in the images table?
You don’t have an SQL question, you have a database design question. Need to know a lot more about how you’re going to implement this. What is the application that sits on top of the database–a web app?
Most of the data you list can go into a single table, with the picture number as your primary key. It would also have a foreign key to the category (see next para.).
You probably want another table to list the categories, so you have a fixed set of categories to choose from. That part depends on how your application will work. For example, if your user creates a new picture, they will want to be able to select a category for it (or perhaps create a new category). But you don’t want a user typing in a category from scratch for each picture or you will end up with a huge number of categories. Each category would have a numeric ID which would be the primary key, and a description of the category.
I have no experience storing binaries in a database but you might check into the BLOB data type. If this is a web app, it would be easier for you to store the pictures in individual files on the server instead of storing them in the database. In that case the file name would be an additional field in your table.
Yeah, this will be for a website; basically, the site itself will pull images based on category. I’ll also probably build an upload app to allow the site owner to upload her own images and give the images a Title, as well as a category from a fixed list.
So, for example, one page on the site might be “holiday.php,” which will display all images with the category ‘holiday’.
I would go with ZipperJJ’s suggestion of the CategoriesImages table as well, because this will cause you not to be constrained by your tables in categorizing images.
A few reasons include : eliminate the chance of making a typo in the category and having the image not come up in lists (you type in LANSCAPE instead of LANDSCAPE); allowing you to change category names easily (PICS OF MY SWEETIE becomes PICS OF MY TWO-TIMING EX quite quickly); and it might make your queries marginally faster.
PIC_ID – Primary Key for the picture generate this from a Sequence don’t put it in by hand. TITLE DESCRIPTION – Title might or might not be meaningfull, could allow for better searches FILENAME LINK_URL – Might come in useful
Table: CATEGORY
CAT_ID – Primary Key, from another sequence CAT_NAME CAT_DESCRIPTION PIC_ID – Foreign Key to PICTURE
To fetch back pictures of aktep’s ex you do:
select filename
from picture, category
where picture.pic_id = category.pic_id
and category.cat_description like '%cheating ex%'
Oops :smack: Normalise out the cat_name and cat_description into a cat_detail table. I started out with a third table***** and then tried to make it work with only two. It doesn’t. If a category is going to have any attributes (like I gave it) then you need a third table for them.
The category table should just be
CAT_ID – foreign key to CATEGORY_DETAIL
PIC_ID – foreign key to PICTURE
Which is what you suggested.
*****and wrote out the query to fetch the cheating-ex picture and decided it would confuse the OP. Though probably not as much as a broken design.
I think it can be done nicely with 2 tables; you just got the foreign key in the wrong table. Category is the parent table; Picture is the child – so the foreign key goes in the child, linking to its parent. Each category has multiple pictures, so it wouldn’t even be possible to put a pic_id in the category table.
The above is assuming that there’s a one-to-many relationship between pictures and categories. If there’s a many-to-many, then we do need a 3rd table to resolve this, with just pic-id and cat_id.
Not sure exactly how your website will work, but you probably don’t want a separate php file for each category. You probably want a page where a user can select a category, and a display page to display the pictures in that category. The URL of the display page would be something like “DisplayPic.php?cat=Holiday” rather than “Holiday.php”
The elitist snob answer is that it’s called data normalization and That’s Just How it’s Done.
The practical reason that data normalization is good is that you should never duplicate data anywhere in your database, because this leads to opportunities for problems if for some reason some data changes (and also for efficiency and space reasons). If you repeat values, you have to worry about making sure they stay the same and what happens if some of them change, and this gets to be a real problem if and when you want to change existing values (such as renaming a category), or perform complex queries based on category membership.
As has been pointed out already, foreign keys are a real good thing in this regard. Maintaining a seperate list of categories and knowing for sure that you can’t accidentally delete a category that has items belonging to it is peace of mind worth having.