错误报告数据库的数据库结构[关闭]

Question

As a personal project to improve my knowledge of php, OOP and PDO i am going to design a bug reporting database like Atlassian Jira, fogbugz etc

I have basic understanding of database structure however i have only ever worked with a single database.

In this simplified scenario lets say we will have the following tables:

1. Users
2. bugs
3. project
4. issue type
5. priority

My first thought was i would need a new database for each company because if i mixed the bugs database with everyones bug it would affect the return of query speeds. That could lead to having lots of databases, since this is a new concept i just want to see if there is a better way to do it.

I assume i will auto generate a new database with the default populated fields for the 5 tables above under a database called something like db_company_56 where 56 is the id of the company.

Could get a bit messy maintaining things with so many databases, i wonder is there a way to store them under a subfolder in phpadmin, from looking around i dont see an obvious way. So is the route i'm taking reasonably ok or is there a better way to do something like this?

If you have further reading material i can search on myself that would be great, i just dont really know what keywords to search for this under.

Answer 1

There are a few ways to approach any situation when setting up a database for it, but here's how I'd set it up if I were in your position. As mentioned in the comments, you're thinking a level higher than you ought to be--unless security is a major issue and you don't want to store different company records in the same database, you should need no more than one database for all the information.

I'm assuming companies will have multiple projects.

What you'd want are, as str mentioned in the comments, multiple tables. I would create one table for users and their respective data and one table for bugs, with columns for company, project, issue type, priority, any other information you may want (reporting user, etc.), and one more column: an id number relative to company, or more likely, project. This can't be an AUTO_INCREMENT column but it can still be managed (I'll explain in just a moment).

You can display bugs for a single project in a company by using multiple WHERE conditions, for example:

SELECT * FROM 'bugtable' WHERE company = 'company_name' AND project = 'project_name' ORDER BY bugid ASC

This will display all results from the specified company's specified project. When adding new bug records, it's easy to assign a new id number. Assign a variable to a query finding the highest id, for instance:

SELECT 'bugid' FROM 'bugtable' WHERE company = 'company_name' AND project = 'project_name' ORDER BY bugid DESC

Then, increment the id by 1 and use it to submit a new bug record. In short, all you need to store bug reports is a single table. Hope this helps a little!