You wrote that you are not limited to relational databases. You could consider using a graph database, like neo4j. Graph databases are particularly well-suited for situations like yours where relations are more important than, say, averages and sums.
You can find an introduction here and a working example here.
A more complex, but more efficient structure that you could use is graphity.
Sounds like you have 3 major categories of data you are trying to store:
- General job data (job id, job requester id, job receiver id etc)
- State transitions (job started, job finished)
- State-specific job data
- (optional) job-related events (price changed, job receiver user reassigned etc)
The key is to separate event-like data from everything else.
Schema design
Here are some details:
1. General jobs table
All of the information that's NOT state-specific goes into the (let's say) "jobs" table. Auto-generated primary key: job_id
2. State transitions table
All information about state transitions goes into "job_state_transitions" table, which might have the following columns:
- job_transition_id
- job_id
- created_at
- from_state
- to_state
Ideally this table is append-only. Nothing is every updated or removed here.
Using such a table, you can find out the latest status of any particular job by selecting the latest row for a given job_id from the job_transitions table. You can further denormalize that and introduce a "job_state" column, the contents of which are updated every time a new row is inserted in job_transition table (stored procedures might help here if that's your thing).
You can also do all sorts of analysis on state transitions, because the timing data is preserved (created_at is a date/time field that can help with that)
3. State-specific job data
All of the state-specific data goes into "[state]-jobs" tables. Primary key: some sequence id. Main index: job_transition_id
4. Optional event data
You can also introduce an "audit trail" table that allows you to keep track of various changes that users might request for each job, like your "change agreed upon price". This is a generalization of the state transition table: one main table that contains events and a one supplemental table for each event type (for example, price_changes table with job_id, created_at, from_price and to_price columns).
Scaling to millions of users
If the main "jobs" table grows to be unwieldy, you can shard it by job_id or requesting_user_id or something like that.
Likewise, events table should be append-only and can be rotated or purged of events related to jobs that have been finished.
Best Answer
You should never use multiple columns to hold more than one of the same item. This is bad database design and will be a major pain to use (try to write a query that will fetch and display all of a user's photos, or delete one photo, and you will see what I mean).
Another issue is that you might want to store other info about each photo--such as when it was uploaded and what format it is. Instead of 20 columns, suddenly you might have 60 or 80 columns in your table. Ugh!
Instead, have a table of photos that is separate from the table of users. This is linked to the user table on user ID. You can enforce the 20 photo limit with a table constraint, or simply by checking the number of photos programmatically before you allow upload.
Table user:
Table photo:
If you set the foreign key for photo to
on delete cascade, a user's photos will automatically be deleted if you delete the user.See this StackOverflow question for info on a table constraint to limit the number of photos per user.
Getting the photos for a user uses a simple query:
If you need photos and user information like the name, use a join:
Update: I do agree with Pieter B that, in most cases, you are probably better off storing the images separately in the file structure, and just making the
imagestable store the file name of each image. This will make it a lot easier to, say, display the images on a web page. But you will also need extra code to make sure the directory stays in sync with the table--if you delete a user, you have to go manually delete all of the images.