1

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.

Improve this question
2
  • By ambiguous I meant it wasn't clear which entity/attribute was the referring and which the referenced. The new diagram is much better. Comments withdrawn.
    – Michael Green
    Nov 13, 2017 at 23:35
  • I'd suggest it should be Languages --< apps_languages rather than what's drawn.
    – Michael Green
    Nov 13, 2017 at 23:36

1 Answer 1

Reset to default
1

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!

Improve this answer
5
  • Thank you for a helpfully respond! I'll change these lines and use lowercases are you advised. Databaseanswers.org looks very good and I'll fix my scheme based on it. What can you say about AppPlatform, AppLang, AppAttach tables? Is it correct or should I use data dictionaries? My college friend said that it's not allowed to use tables like that. I don't know why.
    – mathsicist
    Nov 12, 2017 at 21:23
  • I don't know whether the stackxechange's rules allow for editing the question and updating the scheme so I'll upload a new open as an answer. What do you think about it?
    – mathsicist
    Nov 12, 2017 at 21:29
  • Yes, you can always edit your own question. Be sure and put @Vérace in a comment so that I see any edit to the question! If my answer was helpful, you can upvote it and/or mark it as correct!
    – Vérace
    Nov 12, 2017 at 23:37
  • please take a look at the updated scheme. Unfortunatly, I can't upvote because my rank is too low.
    – mathsicist
    Nov 13, 2017 at 14:57
  • It looks good to me - but then, I haven't written any code to extract meaningful data from your schema - I presume that's part of your course work :-). You will find that you are trying to perform function A and that you'll need to change table x. This will have an effect on function B (already working) and tables y and z... Also, you could store the data all in one table (NOT ADVISED) and use very complex app code to do virtually anything (see EAV)! The best solution (IMHO) is to put as much complexity into the db as possible - it's a database project - use the database! HTH.
    – Vérace
    Nov 15, 2017 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.