Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Start here: <a href="http://support.microsoft.com/kb/283878" rel="nofollow">http://support.microsoft.com/kb/283878</a></p> <p>You never want to store calculations directly in a table, the calculations will be done by the queries that generate your data. Table 3 is actually the results of a simple query.</p> <p>From what you have above, I would say you have at least the following three tables:</p> <p>Customers - contains basic customer data, one row per customer, and some kind of ID that you generate (probably an integer identity column is best, but can be anything you choose).</p> <p>Produce (or Products, if you have more than fruit and vegetables) - one row per product containing product information, some kind of ID that you generate (again, integer identity is the best).</p> <p>Sales - one row per transaction</p> <p>Here's where things get a little tricky. You could have Sales and SalesItems where sales has a unique row for each transaction and SalesItems contains one row for each product that was purchased in the transaction, but for a really simple system as you appear to have above, you could just get away with a Sales table that has ProductID and CustomerID along with quantity and whatever else you want to store.</p> <p>EDIT: It's a safe bet that a simple system will one day grow into a complicated system, so if I were designing the database I would probably go with separate tables for Sales and SalesItems to make the database more flexible and able to scale better.</p>
    singulars
    1. This table or related slice is empty.
    1. PONeed help on how to structure my database (SQL Server)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USPam Lahoud
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PONeed help on how to structure my database (SQL Server)
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThank you. I think this makes a lot of sense. In the end, I care most about tracking performance by group, like how "fruit" has done compared to "vegetables". The breakdown of customer by customer isn't the main goal.
      singulars
      1. PO
      1. USeek142
    2. COYeah I hear you, I've just been to so many customers that stood up a database to solve one tiny problem and before they knew it it grew into their most important application. At some point in the future you never know if you might become interested in which customers buy the most fruit vs. vegetables or something like that. It's always nice to plan the design out in a way that allows it to expand to other requirements in the future.
      singulars
      1. PO
      1. USPam Lahoud
    3. COWhen you say you never want to directly store calculated data in a table, do you mean to that I should create a separate table for such data, or that there is some way to generate the values in Table3 without ever storing the calculated columns in Table1 & Table2?
      singulars
      1. PO
      1. USeek142
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload