Please review the logical data model

database-design

I have to design and implement a database on the study course. I want to make sure that my scheme is correct. I've created a model company and a system which needs a database.

A little background about the database: The database is designed for the company who test applications for external clients. Let's say that the company offers performing acceptance and compatibility testing services. The workflow of the company:

  1. Seller (Client) requests for testing his application
  2. The ticket is created
  3. Ticket contains information about the application and what should be tested
  4. Test Manager decides which Tester should test the application (ticket)
  5. Tester reports defects to the application
  6. When testing is done, tester set a result

Other businesses requirements:

  1. Seller can requests for testing the application more than one time
  2. Only one tester can test the application (ticket) in one iteration
  3. History of testing should be collected and available

The updated model:
enter image description here

I'm not good into database designing, it's my first bigger project. So forgive me a basic mistakes 🙂 If the diagram is not readable so I'd be glad to hear some advices how to make it pretty. Also, if someone see some weak points, please comment it.

Best Answer

I have a few general points.

Firstly, your diagram is a bit confusing! There are lines which merge into each other and the source and destination tables aren't clear.

Secondly (and this is a matter of opinion), you might want to use <table_name>_id as your PRIMARY KEY (e.g. ticket_id). It removes ambiguity, makes debugging easier and SQL easier to write and particularly read. My own personal convention is to use lower_case_with_underscores_id (and lower case for identifiers generally) and UPPER CASE FOR SQL. This makes code a pleasure to read and almost self-parsing.

Also, take a look here, it's from databaseanswers.org which can be a good first port of call for database modelling projects! Try and make your diagram look a little bit more like that!

Finally, you can download F/LOSS applications for bug-tracking - there's more of them than you can shake a stick at! Again, personal recommendation, you should look at Fossil - it's by D. Richard Hipp, the same guy who's given the world SQLite!

I've watched some podcasts by him and he's impressive! At least take a look at his schema - you can be sure that thought went into it! It's BSD licenced, but I'm not sure if your professor/lecturer would be happy about you claiming his work as your own, but if you clearly refer to it, that should be OK! :-)

Be prepared to make a lot of changes - as Helmuth Von Moltke said, "no plan survives contact with the enemy" - best of luck with your project!

Related Question