Models + Database design

What I want to accomplish:

• Images that should be either single or in albums
• Browseable list of albums and single pictures with page numbering
• Not show images inside albums on browse, only album cover

My approach so far has been:

• Models that represent a single image, and single album
• One database table content containing id, title, thumbnailFile and imageFile etc.
• One database table album with album id, album title etc.
开发者_如何学运维
• One database table album_content mapping what content is inside what album
• One database table browse with ids for albums, and ids for content not inside album + copied attributes used for thumbnail preview and sorting (filenames,titles,views,date etc.)
• Paginator utilizing the latter table only, while view is utilizing the model pointing to content table

I don't think the above is too bad regarding speed, but I don't find it particulary elegant, and I'm looking for a better way to do it, and hopefully shrink the amount of elements to cache/invalidate. So far I've been thinking something along the lines of:

1. Only having one copy of data in the database (combine content, album and browse somehow and still be able to quicly count and sort the dataset by date,views etc.)
2. Stay away from joins on any sortable/order by column
3. Treat all images as a single model instance, also inside albums

My main problem here is that albums have dates, views etc. independant of the content inside it, and I wish to order by the date of content not inside albums + the date of albums which should have a unique identifier. Albums also have colums not relevant for content.

Is there a good way to solve this?

*Edit: For speed, I think I'm stuck with the separate browse table. Is there a way to have Zend dbTable reference the view column of browse <-> album and content so that onUpdate in either album or content is utilizing the CASCADE logic in Zend to update both tables?

I would code in backstage : 1 single image = 1 album typed as single. Thus browsing through single images is the same as browsing through albums. The only difference is when you display a "single image" album or a "normal" album. It makes sens since both albums and single image has one image the represent the contents (the image or the cover).

Tables could like this:

t_album (id, type, title, cover_id, ...)
t_image (id, link, thumbnail_id, ...)
t_album_contents (id_album, id_image, comments)

Note that table [t_album_contents] is necessary only if an image may be in several albums or may be several times in the same album. Otherwise this table may disappear and be replaced with a foreign key on [t_album] in table [t_image].

it's allways a good idea to stick to a normalized database (3NF) first, and the do runtime optimization based on the DBMS you use (violate 3NF only if you really have to) ...

again, i can't make this point clear enough: avoid violations of 3NF at any acceptable cost

if you add something that violates 3NF, make sure that the 3NF part of your database remains intact in every possible situation ... your redundant "copied attributes" could lead to inconsistant data, therefore make sure that the data in the redundancy free 3NF part of your model has the priority ... eg. regulary rebuild redundant parts from 3NF parts when server-load allows it or provide a tested update schema that makes sure that updates are ONLY applied to the 3NF part, and every update on the 3NF part triggers a rebuild of the corresponding redundant data ... approaches like this try to minimize the risk of data corruption when you need redundant data for reasons like performance while reading from the db

it's also a good idea to seperate the 3NF part of the DB from the other part (by namespace/prefix/etc)

depending on the expected request (# of browse-request vs. # of update-/insert-requests) it could make sense to use your suggested approach with redundant data or not. don't underestimate the right table index when it comes to speed.

for the problem of comparing (sorting) albums mixed with other content:

why can't a content entity represent an album? an album is like a directory ... it could have subdirectories which would be content from the directories point of view ... if it's modelled this way you would compare content to content ... extra attributes, depending on the particular content type can be in another table with a 1:0-1 relation while the "common" comparable attributes reside in the content table

on the other hand you could use a union vor this which would ideally be only a little slower but could make both types of entities uniform to be compared and sorted ... depending on the expected requests it could make sense to cache those sorted results for every browsable object

i hope this helps a bit