mysql – Should i change anything in this db design?

You have given us very little information about your requirements but Ill try to make some suggestions based on my (very limited) experience.

You need to logically think about how you are going to use your database and create your design from that. In my opinion, a Project is the centre of the system, so everything should be thought of around entity.
- A Project has many Sprints.
- A Sprint has many Stories.
- A Story has many StoryTasks.
- Each StoryTask has an assigned user.
So taking from this concept, we already have a simple design:
```
Project --< Sprint --< Story --< StoryTask -- Person
```
Then you can go into more detail, thinking of things like Do I want my user to be able to work on more than one task? If the answer is yes, then you can have a one to many relationship from Person:
```
Project --< Sprint --< Story --< StoryTask >-- Person
```
Not that you have your basic design that you think would work, you can think in more detail about your queries. For example, if you want to get a list of all the people working on your project, you would have to write a long query which joins and searchers all of the tables involved. For a commonly used functionality such as listing all people on a project, you can create a join between Person and Project:
```
Person* >-- Project --< Sprint --< Story --< StoryTask >-- Person*
```
Also, if you are planning on a person to be associated with more than one project and be able to query which projects a person has been part of you would need to create a relationship table
```
Person* --< ProjectPersonnel >-- Project --< Sprint --< Story --< StoryTask >-- Person*
```
It is always better to think of the queries you will be running before designing your database. For example, if you wanted a list of all the Projects a Person has been on, you could have a relatively simple query
```
SELECT
proj.ProjectName
FROM
Person per
LEFT JOIN ProjectPersonnel pp
ON per.PersonId = pp.PersonId
LEFT JOIN Project proj
ON pp.ProjectId = proj.ProjectId
WHERE
per.FistName = Bob
```

If you wanted a list of all the Stories associated to a Sprint in a Project, you have the structure in place to create a simple query for that

    SELECT
    story.StoryName
    FROM
    Story story
    LEFT JOIN Sprint sprint
    ON story.SprintId = sprint.SprintId
    LEFT JOIN Project proj
    ON sprint.ProjectId = proj.ProjectId
    WHERE
    proj.Name = MyProject1
    AND
    sprint.Number = 3

As I say, I havent got much experience in the field so there are definitely better suggestions out there – I just hope that this is somewhat useful.

I suggest to locating id field to the first position, every table should has primary key, and every primary key should has different name with each other.

Like: project => project_id , person => person_id, …….

This action would facilitate you for database query and application that you deploy.
Hope this helps.

mysql – Should i change anything in this db design?

mysql – Should i change anything in this db design?

Leave a Reply Cancel reply