Database design considerations


I work on a website where traders can insert their trades results and see them in charts and graphs.

All users trades go into one table.
Users are in another table
Should I have a table for users in trial period and another table for subscribed users or shoult this data be logically in the same table or maybe this data should be in columns of users table ?

How do I know what data gets a separate table and what gets columns in an existing table?

the same table

i think it comes with experience

the main factor is, how similar are the entities?

if they are just different statuses or versions of the same entity, especially if a given instance can migrate from one status or version to another, then they should be in the same table – otherwise, you have to insert and delete on separate tables, versus just changing the status code or version number in the given row


Perhaps that needs to be clarified. Generally speaking we would not add columns to a table that would not have data for many rows but there are many valid exceptions to that. So it is difficult to generalize.

If there is data that will exist only when a trader is on trial and will never apply when they subscribe then my guess is that you can and should have a separate table for the data that only applies to trials and only that data. You should have a trader id and you can use the trader id for the primary key of the table with trial data.