Page 1 of 1
advise in designing some tables
Posted: Thu May 27, 2004 4:11 am
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
Posted: Thu May 27, 2004 4:17 am
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
Posted: Thu May 27, 2004 4:26 am
by feyd
mmm for many-to-many.. that needs a seperate table on it's own.
Posted: Thu May 27, 2004 4:30 am
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'
Posted: Thu May 27, 2004 4:54 am
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.
Posted: Thu May 27, 2004 5:11 am
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?
Posted: Thu May 27, 2004 5:32 am
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- id
- name
- order
- permissions (potentially)
- whatever
albums- id
- name
- num photos
- submitter
- creation date
- last update
- last update user id
- permissions (potentially)
- parent category
photos- id
- data
- type
- submitter
- permissions (potentially)
- upload date
- description
- title
- whatever else
photo_album(many-to-many)
- photo id
- album id