0

On this question I received an answer that worked well. I am now wondering if there is a possible better structure.

I have two tables.

Projects : id, title
Status : project_id, status_id, created(DATETIME)

At the moment to get the status of my project, I get the project ID and pull the latest row out of the status table based on the project id. To get this latest row is quite a hassle.

Should I rather change the schema to this?

Projects : id, title, current_status_id(FK)
Status : id(PK), project_id, status_id, created(DATETIME)

Then I can just join the tables with the FK and get the row that I want without looking for the latest?

EDIT:

So I want something like this

SELECT * FROM projects
LEFT JOIN status on projects.id = status.project_id
WHERE projects.id = 1

But I want only the latest record in the status table.

EDIT 2:

So I want something like this

SELECT * FROM projects
LEFT JOIN status on projects.id = status.project_id

But for each project returned, only get the latest status record for that project_id from status.

Community
  • 1
  • 1
Zevi Sternlicht
  • 5,399
  • 19
  • 31

2 Answers2

1

How is this a hassle?

SELECT project_id, status_id, created 
FROM Status 
WHERE project_id = the-id
ORDER BY created DESC
LIMIT 1;

Or, if you need a list of multiple projects:

SELECT a.project_id, a.status_id, a.created 
FROM Status a
LEFT JOIN Status b
ON a.project_id = b.project_id
AND b.created > a.created
WHERE a.project_id IN(id1, id2, id3) AND b.project_id IS NULL;

So, with project data:

SELECT Projects.*, Status.*
FROM Projects
LEFT JOIN Status 
ON Status.project_id = Projects.id
WHERE Projects.id = the-id
ORDER BY Status.created DESC
LIMIT 1;

Or:

SELECT Projects.*, Status.*
FROM Projects
LEFT JOIN Status a
ON a.project_id = Projects.id
LEFT JOIN Status b
ON a.project_id = b.project_id
AND b.created > a.created
WHERE b.project_id IS NULL;
Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • That would be using a seperate query. I would like one query joined for speed – Zevi Sternlicht Mar 06 '13 at 21:13
  • Does not compute? Both are single queries for different purposes (so you need only 1 of your choice), and 'one query joined for speed' => I don't get what you mean with that. – Wrikken Mar 06 '13 at 21:17
  • I would love to select the project and have the status come up in that query. Is that not quicker then performing two queries? – Zevi Sternlicht Mar 06 '13 at 21:20
  • So, you mean you have to select the project by other means then a project id? Just join the project table to it then & add the clauses, no biggie? – Wrikken Mar 06 '13 at 21:21
  • 1
    yes - i'm pretty sure OP intends to join this current status to the main record. (agreed not a big thing) – Randy Mar 06 '13 at 21:21
  • And BTW: although it can probably be done in 1 here, 2 queries _can_ be faster then one. Don't fall into the trap of trying to cram everything in a single call. – Wrikken Mar 06 '13 at 21:22
  • @Randy and Wrikken, see edit. You think maybe better in to calls then? – Zevi Sternlicht Mar 06 '13 at 21:25
  • @Wrikken yeah, you are right, but that doesnt let me select all projects without knowing thier IDs. How to do it for mulitple – Zevi Sternlicht Mar 06 '13 at 21:32
  • Give an example where and how you select multiple projects, and I'll add it in for you. – Wrikken Mar 06 '13 at 21:34
1

That's one way to do it.

You might not even need the current_status field to be an FK; why not just store the value? You could get into odd circular references otherwise.

Another way would be to store a status archive, separate of the projects table. Every time the status changes, insert the current status into the archive table, and change the projects.status value.

landons
  • 9,502
  • 3
  • 33
  • 46