Database schema

[gills4prem]

I have done some lessons but not designed a schema before.

I would like some advice for a schema that works with my project in uniity.

I am trying to make a sales tracker for my team at work.

The app has buttons that increases number of kpi’s sold ( I have about 18 to keep track of) and a submit button to send the data to database.

My original idea was to create a large database with one table with userid and all kpis and simply have one line per user and update the total. I would also have a 2nd table that holds username and password ect…

The issue I see is that I wont be able to use dates to collect information and need a new table each month.

Any ideas would be grateful or any advice on websites youtube videos that good for learning.

thank you

[ErnieAlex]

Well, I am not sure what you are asking, but…

Most sales systems use the date of the sales. Normally, you would want to track old sales and compare them to recent sales. Usually over the period of years, not months or weeks. To track sales the easiest way is to include the salesperson’s ID which links them to the data in the USER’s table and perhaps a sale ID which would link to the fully detailed sales record.
For a “sales tracker”, you probably only want the totals of sales for the last week or month and maybe year. To do that you would query the sales data based on date limits. Therefore, you would need the date inside the sales data table. You must have records on individual sales such as date of sale, person who purchased the item and details of the item itself. Using that data, it is easy to query based on a date limit.
Therefore, if you are creating a grid-chart display or pie-chart display, you just need to know the dates involved, such as the last 30 days for a monthly chart. The query would just need to pull all dates from now to 30 days ago. One small WHERE clause to check if the date is 30 days ago or less. You do that right in the query using this type of code:

SELECT  * FROM  sales_table
WHERE   sales_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

Just a quick example which you would need to adjust as needed to fit your field schema. Hope that gets you started…

[phdr]

Here’s a general approach to creating database schemas - databases are for holding data. Your task is to record the relevant Who, What, When, Where, and Why information about each data point. Once you have recorded this data, you can then write queries to produce any report you want about that data.

For sales data (creating appropriate columns as needed) -
Who - would be the id of the sales unit (department and/or individual sales person.)
What - would the the item id and quantity of what was sold.
When - would be the date (and time) information when the sale record was created or the sale was made.
Where - any location information about the sale.
Why - any other ‘memo’ type information about the sale.

For any piece of information that can have multiple entries, such as a ‘status’, you would have a separate table, linked back to the sales data through the sales id (an auto-increment integer index in the sales table), and containing the relevant Who, What, Where, When, and Why information about each transaction that affects the piece of information.