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
advise in designing some tables
Moderator: General Moderators
-
davidklonski
- Forum Contributor
- Posts: 128
- Joined: Mon Mar 22, 2004 4:55 pm
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
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
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
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
-
davidklonski
- Forum Contributor
- Posts: 128
- Joined: Mon Mar 22, 2004 4:55 pm
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?
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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
categories
- id
- name
- order
- permissions (potentially)
- whatever
- id
- name
- num photos
- submitter
- creation date
- last update
- last update user id
- permissions (potentially)
- parent category
- id
- data
- type
- submitter
- permissions (potentially)
- upload date
- description
- title
- whatever else
- photo id
- album id