0

I am new to web development field and particularly very poor at database management. I am developing a web application which resembles Project Management System where new companies can add their profile, create a project under them, under each project they can create a new task and so on....

Hence, my question is right now is should i use one common table for all the new profile (abc & co) created and one common table for all the users registered under profile (abc & co) created, or should i create new tables for each when a new profile is created and new table for each users and project created ?

2

If I understand your question well, and you want to build a database by making use of a relational database management system (RDBMS), you should create a table for every entity that you have determined as relevant in your scenario, e.g., one table for Company, one for Person (who are Employees of a given Company), one for Project, and one for Task.

Once you have defined your database structure representing entities by means of tables, you will face the need to store a given ocurrence of an entity, then you should use the corresponding table for doing so. For instance, if you want to store three occurrences of the entity called Company, say “Database Modeling Corporation”, “Application Programming Enterprises” and “User Experience Industries”, then you should INSERT (using DML in SQL) each one of them in the table called Company.

So, no, I would not suggest you creating a new table for storing every particular ocurrence of an entity, since this approach would be very unpractical and inflexible. In this sense, it is best to use the tables that you have already defined for each respective entity.

Every stored row (an occurence of an entity) must be uniquely identified in your tables so that, among other things, you prevent redundancy, therefore you need to use a PRIMARY KEY in this regard.

You also need to set the relationships that exist between the different entities/tables that you are working with, and the concept of FOREIGN KEY is a very powerful relational tool pertaining to this aspect. For example, a specific Task may belong to a particular Project, so yo should add a FOREIGN KEY to the table called Task, and such FOREIGN KEY should reference the PRIMARY KEY that has being set for the table called Project.

0

Your wording isn't entirely clear, but it seems you are asking about what we call a "multi tenanted" design. There are two main ways to achieve this:

  1. Have a separate database for each tenant (i.e. each company using your product). This has the advantage of simple security as each client's data is in its own database so bugs in your code can't accidentally reveal each clients information to each other, and it is also simpler in a number of regards. It can be less efficient though, doesn't easily allow for shared resources (either globally shares amongst all clients, or if you want your users to have control to share data with others), and is extra work to administer (any update must be applied to, and tested on, each of your client databases instead of just one)
  2. Have all the clients in one database and make sure that the application only shows people the right data.
  3. The mid-way point is available in some RDBMS: using a different schema per client.

In a database system that doesn't support schemas I would avoid trying to fake the same effect using differently named tables per client - that is just combining the bad points of the other options with the added complication of your table/view/procedure names no longer being static from the point of view of your code outside the database.

For some details about multi-tenancy, see https://msdn.microsoft.com/en-us/library/aa479086.aspx and https://en.wikipedia.org/wiki/Multitenancy, amongst many other resources (don't worry about that first link being from MS if you are not using SQL Server: the content of the article is not at all MS SQL Server specific).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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