MySQL interesting search

Question

I have two tables, applications and applicationRevisions, structured:

applications (id, time, user, views)
applicationRevisions(id, application, time, user, name, description)

The latter holds 'revisions' of an application page, containing title and description, so it's a one-to-many relationship. When an application page is shown, the latest applicationRevisions row with the matching application ID is chosen.

However, I have no way of knowing if an application with a certain name exists at any particular time because previous revisions may have different names, and the name is not stored in the applications table.

I have a workarounds; store the current name as a field in applications, and when an application is edited, add the row as usual to applicationRevisions, but then update the name in the applications row.

Is there a better way to do this?

Answer 1

So, you'd like to search for a name in to get the application and then get the most recent revision of that application, which may no longer have that name. This is certainly possible with subqueries but what are you going to do about applications which happen to have the same name for some revisions?

Would be much more clear if the application table could hold the name and description. To be honest, this could just be a single table since time and user in application would likely just be the same as used for the first revision of each application. Only the views field is left and that could be in a table of just applications_views (application, views).

Anyway, if you want to avoid major changes to the schema and the name confusion between applications is OK, you could make a query something like this:

select * from applications join applicationRevisions
on (applications.id=applicationRevisions.application)
where applicationRevisions.id in 
   (select max(id) 
    from applicationRevisions 
    where name = 'foobar' 
    group by application);

If I guessed the relationships correctly, this will give you all fields from the most recent revision of each application that ever used the name 'foobar'.

Answer 2

Correct me If I am missing something here, every application entry must atleast have a applicationRevision right ?

Why not use foreign key constraints ? Make application field of the applicationRevision table a foreign key. Identify application with a id and not a name. Make name the property of the revision.

So lets say you want to search for a application which has a name "wxyz", so you do a

select id,application from applicationRevision where name="wxyz" order by time DESC LIMIT 1;

This gives you the application id. You can do a JOIN and get application fields from a single query