New to BASE - Need help with a database design

[ramon82]

Hi all!

Its the first time I am using BASE for a database design. What I have in mind is to create a database with a minimum of THREE tables : CUSTOMERS, PRODUCTS and ORDERS respectively. Example:

Customers: CustID, Name, Surname, Address, EMail, TotalSales
Products: ProductID, ProductName, ProductDescription, UnitPrice, UnitsInStock
Orders: OrderID, ProductID, QtyOrdered, OrderTotal

By now you may have noticed what I have in mind. Basically I need to design it in such a way that I could:

1- Input new customers and delete when needed
2- Input new products and delete when needed
3- Create new orders by generating a new OrderID and based on the ProductID*QtyOrdered the result is stored in OrderTotal and accumulates with TotalSales (Customers)

It's like keeping a record for each client on how much products he bought and the total revenue made. I would then create reports and queries accordingly but I need to know how calculations are done and stored first.

I know this has something to do with relationships etc but I am lost. Can anyone help me?

Thanks a lot
Ramon

[Villeroy]

Welcome to the forum. The ultimate tutorial on database design in the context of OOo Base is this one: http://openoffice.org/projects/document ... 20tutorial

[DACM]

Does each Order really involve a single Product?

If an Order may involve multiple products:
Customers: CustID, Name, Surname, Address, EMail {TotalSales removed, see below}
Products: ProductID, ProductName, ProductDescription, UnitPrice, UnitsInStock
Orders: OrderID, CustID, OrderDate, ShipDate
OrderProducts: ID, OrderID, ProductID, QtyOrdered {OrderTotal removed, see below}
See: one-to-many-to-many relationship?

Perhaps read "Some Review and Considerations" pertaining to Forms/SubForms here:
[Example] Filter/Search with Forms (leveraging SubForms)
You'll also find valuable links to "More information" there.

We generally don't save computations to a Table, but use Queries for such tasks:
TotalSales using Query by aggregate sum, group by customer
OrderTotal using Query with computed column
See:
[Tutorial] Calculations in databases (quick primer)
Creating Calculated Fields in OpenOffice Base
Combining and summing fields in OpenOffice.org database queries
Calculated Fields

A simple Sales Order Form would have an input area with List Boxes for Customer selection and Product selection. A SubForm based on a Query and linked by OrderID is used to host a Table Control (grid) showing all selected Items, quantities (Input Box), prices, and "horizontal sums" (extended price=quantity*price). Another SubForm based on a Query is used to compute the "vertical totals" (such as OrderTotal) using SQL aggregate functions.