Quantcast

Maximum PC

It is currently Thu Jul 10, 2014 3:58 am

All times are UTC - 8 hours




Post new topic Reply to topic  [ 33 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: database design question for the MySQL users...
PostPosted: Wed Oct 06, 2010 8:47 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
I am in the process of creating a custom photo gallery system for a website, and need a little advice on how to set up the tables in mysql.

I currently have two tables: one is called "Albums" which contains a list of photo albums and a table called "Photos" which contains maintains a list of actual physical files on the server and a unique file_id associated with each picture file.

What I am trying to decide is what is the best way to associate the Albums with the Photos.


Here is how I'm thinking the tables should be set up (disregard the actual wording because I know it is not executable sql commands, but more a way of describing my database layout):

First, the Albums table:
Code:
Albums (
  album_id int not null primary key,
  album_title text not null,
}


Next, the Photos table:
Code:
Photos (
  photo_id int not null primary key,
  photo_filename text not null
)


And (I am thinking of creating this table to sort of "connect" or "map" the two tables together):
Code:
Photos_Albums (
  photo_id int not null,
  file_id int not null
)


--or should I just do this (and forget about using the Photos_Albums mapping table)---
In the Photos table:
Code:
Photos (
  photo_id int not null primary key,
  photo_filename text not null,
  photo_albumid init not null
)



What is the recommended/appropriate method for trying to "connect" or map the Photos and Albums table together?

(FYI: I'm still in the processing of trying to teach myself MYSQL and relational database design, and the Albums and Photos table mentioned above actually contain a few more fields, but the fields that were omitted were not significant to the question/example).


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 07, 2010 4:05 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
http://gallery.menalto.com/

:)

But to answer your question do you want to have a photo be part of one or MORE albums?


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 07, 2010 2:17 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
But to answer your question do you want to have a photo be part of one or MORE albums?

Bingo. This is the type of question you should really be thinking about at this point. After you've thought about these more abstract issues, and preferably, created an ERD, the physical database schema really just falls into place (ie it's a mechanical process).

Some others things worth considering...
Will you allow duplicate album names?
What about duplicate photo filenames?
What kinds of queries would you like to run?


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 07, 2010 5:45 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
I've already have it set so that duplicate album names and picture files cannot be duplicated (nless of course the user has the same picture but under two different physical file names).

As to answering some of the questions... In some rare cases, I could see where a photo could belong to two albums (ie. if someone takes a vacation to attend a wedding... they may have some photos which are a little of both and thus may want to put them into two albums).


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Fri Oct 08, 2010 9:30 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
cbassett01 wrote:
I've already have it set so that duplicate album names and picture files cannot be duplicated (nless of course the user has the same picture but under two different physical file names).

As to answering some of the questions... In some rare cases, I could see where a photo could belong to two albums (ie. if someone takes a vacation to attend a wedding... they may have some photos which are a little of both and thus may want to put them into two albums).


You should probably handle that with tags of some kind. However without thinking about it you will just want an association table that you can link photos to albums.

I would put 4 fields in it:
Code:
ID INT IDENTITY(1,1) PK
AlbumID INT
PhotoID INT
DTS DATETIME


This way you can tell when the item was added to the album. To display the album you would just do something like this:

Code:
SELECT P.*, A.DTS AS [Added Date] FROM Photos P
JOIN AssociationTable A ON P.ID = A.PhotoID
WHERE A.AlbumID = @AlbumID


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 14, 2010 12:06 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
cbassett01 wrote:
I've already have it set so that duplicate album names and picture files cannot be duplicated (nless of course the user has the same picture but under two different physical file names).

Why? Is this a single-user system?

What does the name of a physical file have to do with the name of a picture? Are you really going to make me change thousands of filenames so that my pictures work with your app? You need to think about things like this... you're going to run into a bunch of annoying problems if you don't think it through before you start implementing the database.

A similar thought. Are you also going to assume that the date for a file is the timestamp for the physical file? That might create some confusion when someone has automatic dating of pictures turned on in their camera (ie the little yellow date in the corner of the pic thing), but they don't transfer the image files to their computer until later for whatever reason(s).

cbassett01 wrote:
As to answering some of the questions... In some rare cases, I could see where a photo could belong to two albums (ie. if someone takes a vacation to attend a wedding... they may have some photos which are a little of both and thus may want to put them into two albums).

This sounds more like tags or labels than alblums to me.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 14, 2010 3:27 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
So to refresh my memory---so to group photos into libraries/albums, i would simply find photos that have similar tags (or a specific tag I set) correct?

I'm thinking the tagging system is thw way to go with this....

Now, one more question, as for user names, I have it so people enter a username and email address they want to use on the system. Now I currently have the email address as the primary key (since there can only be one email address), and the username as unique (to prevent multiple records with the same username/email combination). Now, I also have it set so that people could change the username and/or email address associated with their account...

My concern is keeping up with standards and recommendations for good database design... Should I assign people a number (such as an auto-increment value) that will stay with the user regardless of what username/email they use, until their account is deleted... Is this a better approach for using a primary key than having the username/password for primary/unique fields to prevent duplicate users?


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 14, 2010 5:36 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
cbassett01 wrote:
So to refresh my memory---so to group photos into libraries/albums, i would simply find photos that have similar tags (or a specific tag I set) correct?

I'm thinking the tagging system is thw way to go with this....

Well, I think you're at the point now where you're starting to realize there probably aren't any easy answers. I would highly recommend spending some time looking entity-relationship diagrams (ERD) and learning from them. Also, review 1st through 3rd normal forms. Spend some time thinking about what kinds of queries you'd like to perform on the database. Whatever you do... Don't worry about to create the physical tables. It's just a waste of time at this point.

My personal opinion is that getting database design right the first time around (or at least 90% right) is much more important then getting a program's design correct the first time around. With a program, you have tools to help refactor code, a lot of changes can be made behind the scenes without effecting your customers, etc. Bad databases cause all kinds of problems... it forces people write retarded/unorthodox queries, use lose meta-information, sometimes you have to go in and rename 100's or 1000's of files, etc. A total pita when done wrong.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Fri Oct 15, 2010 7:53 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
Don't use the email in the PK. I HATE when systems make it hard to change your email.
Don't let them change their username...

You should have an int identity field called "ID" on EVERY table as the PK, PERIOD. Also it is a good idea for a updateDTS.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Fri Oct 15, 2010 1:12 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
I think the integer ID is a good idea for each table, that way people can change pretty much whatever the want without risk of corrupting the system or duplicate data. I'll obviously just have to make sure that when such a change is made, that there is not already an existing username/email that's the same.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Sat Oct 16, 2010 11:45 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
Don't use the email in the PK. I HATE when systems make it hard to change your email.
Don't let them change their username...

Crashtech brings up a good point here. Some database designers, and even theorists, are adamant about never changing a primary key. Others, who are perhaps more pragmatic, will accept the notion and change a primary key as needed. It's almost a personal decision.

CrashTECH wrote:
You should have an int identity field called "ID" on EVERY table as the PK, PERIOD. Also it is a good idea for a updateDTS.

Hell no! All that does is create painful queries, costly mistakes when you just happen to have a typo that is also a PK (which eventually induces paranoia btw), and makes the database artificially larger than it ought to be. For two years, I worked for a "database group" at Boeing that did this with their older schemas. There were 100+ tables, each one of them using a "sid" as the pk, and it was easily the worst database experience that I've had in my life. When we created a new schema that used "natural keys", some of the systems engineers thought we had done something magical. Little did they know...


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Sat Oct 16, 2010 12:42 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
Well if the email thing as the primary key doesn't mess things up too much (or isn't really against general practice) then I may just go this route, simply because each email address that exists is unique and cannot be repeated (as in, there really can't be two jdoe@mywebsite.com addresses that exist at the same time on the Internet--the only exception is if two people happen to be running personal web servers locally that use the domain mywebsite.com), but if you had an @yahoo.com or @gmail.com address, your address would be unique across the Internet). That's how I see it, and that was why I had proposed using an email address as a PK.

As for the changing of the PK, I know that it's probably bad practice, but rather than creating extra fields in the tables to create a PK, whjy not just use the email. Yes, it can be changed which may be frowned upon by some, but I'd obviously make sure that this change was carried out throughout any table that referenced the email address as a PK or foriegn key. Plus, using a bunch of numerical IDs may get confusing for me after a while, especially when you start having tens of hundreds (or even 100+ users).

Now, simple question about the "varchar" in MySQL: If a define a field the type of varchar(128) and I store something in that field that was maybe only 25 characters, does it still use up the 128 characters, or only the 25 I used with the data??? (My intuition & previous programming experience wants to tell me that I'd only be using the 25 characters, hence the name "varchar" or variable character). While size isn't much of an issue at this point, I don't want to go wasting byte-space in case it does become an issue in the future...


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Sat Oct 16, 2010 11:20 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
cbassett01 wrote:
Now, simple question about the "varchar" in MySQL: If a define a field the type of varchar(128) and I store something in that field that was maybe only 25 characters, does it still use up the 128 characters, or only the 25 I used with the data??? (My intuition & previous programming experience wants to tell me that I'd only be using the 25 characters, hence the name "varchar" or variable character). While size isn't much of an issue at this point, I don't want to go wasting byte-space in case it does become an issue in the future...


Your intuition is correct; however, that doesn't mean a DMBS isn't using a fixed array below the surface. The DBMS is providing you with another level of abstraction so that you don't have to worry about it.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Sun Oct 17, 2010 7:42 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1452
One more quick question: relating to use of tags...

Do most developers store the tags in a text field (I'm storing them in a MySQL database) or should I create a seperate table that links the tags to the specific elements they are related to (which could become time consuming and take up excessive space in the database)?

I've tried storing them in a text string and then have PHP parse the text string to match tags and words, and it's worked fine so far. I obviously chose to use a deliminator, the semi-colon, to split up phrases in the tags text field so that when PHP splits or tokenizes the string and looks at each element, it knows how to split the string up for proper manipulation.

Is this the best way to go about this, or is there a faster (or less clunky) way of doing this?

As a side note, I have a seperate search function set up to search the descriptions and titles of the photo albums as well, so they can be searched for using this method as well (this is just a simple linear search that looks for the terms within the titles and descriptions of the album's title field data and Description field data in the Albums table).

Basically, I'm trying to create a system that a user can select photo albums based on a set of key words and get the appropriate results using various functions in PHP and MySQL such as using the "LIKE" clause in MySQL.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Mon Oct 18, 2010 12:21 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
cbassett01 wrote:
Do most developers store the tags in a text field (I'm storing them in a MySQL database)

We've already established that most developers can't solve fizzbuzz, so I wouldn't use that as a guide for doing something. What you should be asking yourself is what are my options and how would the best developers do this, instead!

cbassett01 wrote:
or should I create a seperate table that links the tags to the specific elements they are related to (which could become time consuming and take up excessive space in the database)?

Let's pretend that you have a group of 20 photos which contain the same three tags. Let's also assume that each of the tags is 6 chars in length, so with your two delimiters, you're looking at 3*6 + 2 = 20 bytes per file or 400 bytes total by storing the tags separately in their own field. Plus, the query now resembles an O(n^2) operation... for each photo, for each tag in the photo's tag list, if... which isn't going to be very efficient. Would the database do this step in O(1) for you instead? It seems to me that the straight-forward m:n relationship using a mapping table is going to be just as efficient in space, more so in time and a lot easier to implement and maintain.

Even a simple search query starts to look a bit hairy, right? You either have to pull back a good chunk of data from the database to parse the tag string or write some pretty hairy SQL code. The former results in either two queries: pull all the tag strings, then follow up with a query for the matching files; or, I guess that you could keep all of the tags in app server and the photos on the database, and somehow, try to keep everything in synch and working... is this going to be your new daytime job?

Also consider what you're going to need to do when someone wants to change the tags for an existing file.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Tue Oct 19, 2010 4:41 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
Here is something to look at, tell us what you think about it...

Code:
tags_table
ID INT Identity(1,1) PK
tag_name varchar(50)  <-- I would put an index on this, since it will be used in where clauses

file_tags_table
ID INT Identity(1,1) PK
tag_id INT FK
file_id INT FK

file_tags_view
SELECT t1.*, t2.tag_name FROM file_tags_table t1
   JOIN tags_table t2 ON t1.tag_id = t2.ID

SELECT * FROM file_tags_view WHERE tag_name =/IN/LIKE ['tag'/('tag1', 'tag2', 'tag3')/'%partialtagtext%']


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Wed Oct 27, 2010 10:12 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
The "tell us what you about it" was for cbassett01, right?


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Oct 28, 2010 4:49 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
Gadget wrote:
The "tell us what you about it" was for cbassett01, right?
Yes, but I spoz you can comment if you want...

I still don't see what is wrong with having an int ID key.... especially since it gets indexed. Searching for strings and what not is kind of annoying not to mention slow.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Mon Nov 01, 2010 1:10 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
Gadget wrote:
The "tell us what you about it" was for cbassett01, right?
Yes, but I spoz you can comment if you want...

I'll take a pass... =)

CrashTECH wrote:
I still don't see what is wrong with having an int ID key.... especially since it gets indexed. Searching for strings and what not is kind of annoying not to mention slow.

Huh, no way! If you create a numeric id, you have to either do the name->id lookup manually when writing a query, or you have to add the lookup to your query (ie you end up typing the name anyways, plus some other crap). Of course, I'm assuming things like >12 strings, not named "one", "two", etc. =)

In my experience (where there were over 100K named records in some tables), writing the additional query code was a PITA. Plus, if you do the name->id lookup as part of the query, what performance benefit is there? You're actually creating more overhead via the subquery and additional burden on the query processor.

Plus, you make the database ugly and harder to understand. If you need blinding performance, then sure, you have to start making some compromises with the database; Of course, if you really need blinding fast performance, you might want to skip a DBMS altogether.


Top
  Profile  
 
 Post subject: Re: database design question for the MySQL users...
PostPosted: Thu Nov 04, 2010 5:42 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
I have never found it more difficult to use an INT ID for joining tables together etc.

Can you show an example where it is a problem? Why would you have to do any manual look ups? Why do you have to do name->ID? That is silly.


Top
  Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 33 posts ]  Go to page 1, 2  Next

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group