mysql – Should i change anything in this db design?

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?

Leave a Reply

Your email address will not be published.