advise in designing some tables

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
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

advise in designing some tables

Post by davidklonski »

Hello

I am new to MySQL, and would appreciate your advise on what would be the best way to design the following small system (I am using MySQL 5.0)

The system has photo albums which have their own data (name, category, submitter, date, number of photos)
The system also has photos which have their own data (name, size, width, height, date, submitter...)
The photos should be stored once in a big repository.
Photos can be assigned to photo albums, in a many-to-many relationship:
A photo album may contain multiple photos and a single photo may appear in multiple albums.
The system has a set of categories that the albums can belong to. An album must belong to one of the defined categories.

I would appreciate your help
Thanks
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

what i normally do in this sort of a situation is give each item (in your case a photo) an reference number (for eg 3472893749823749) and then use the GET method to retrieve it all

this is actually a very powerful method, and i dont know how i lived without it for so long.

for eg, this is what i would do

ALBUM
album name = myalbum
some other = whatever
some other = whatever
some other = whatever
some other = whatever

PHOTO
referenceid = 34729378492374
album = myalbum
some other = whatever
some other = whatever
some other = whatever
some other = whatever

this would keep a relationship going, you could also add exra fields like album1, album2, album3 for more than one relationship

hope i gave you some ideas

good luck
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

mmm for many-to-many.. that needs a seperate table on it's own.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

depends on how simple you want your DB schema,

im guessing hes asking this because he doesnt know the more complex functions of database 'interconnectivity'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

true.. but he does state
"The photos should be stored once in a big repository.", emphasis added.
"A single photo may appear in multiple albums.", appears later.
so storing album reference with the photo itself is not fulfilling his requirement, that I can see.
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

Post by davidklonski »

Let me clarify:
I do know SQL, I know how to perform Joins.
I am just not very experienced in formulating correct tables.
My big question would be how the categories fit into the big picture?

For example:
I would I extract the names of all the categories in the system?
The system may have 10 categories, but only 2 of them will be used in existing albums.

This may imply that I might need to store the information on categories in a separate table...

what do you think?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

myself.. here's a basic layout.. although I'm not a DB designer, nor am I up on 5 yet... so take it as such

categories
  1. id
  2. name
  3. order
  4. permissions (potentially)
  5. whatever
albums
  1. id
  2. name
  3. num photos
  4. submitter
  5. creation date
  6. last update
  7. last update user id
  8. permissions (potentially)
  9. parent category
photos
  1. id
  2. data
  3. type
  4. submitter
  5. permissions (potentially)
  6. upload date
  7. description
  8. title
  9. whatever else
photo_album(many-to-many)
  1. photo id
  2. album id
Post Reply