Calculation From Various Tables

[angel00079]

I have been asked if it would be possible to create a database in SQL Server. At the moment I can just create tables and join. I think that this would be a great way for me to learn more but I do not want to take it on if I am going to be out of my depth. I have included some examples below and would appreciate some advice on how easy the SQL code should be, i.e. is it too much for a beginner?

At the moment the cost and commission fields are entered manually. The design of the tables is not ideal but I would be stuck with it for the prototype at least.

A supplier sells in different ways e.g. each or box. A PO can be used for more than one supplier. The cost is the unit price multiplied by size. The unit price depends on the type. This is what I think the calculation needs, supplier to match to supplier cost table, the size matched to cost type and finally the order date needs to be with the cost effective and expiry date. The commission is 20% of the cost.

I have looked on this forum and Internet but I am even more confused. I am not sure if I am making it more complicated than it actually is or alternatively it is too much for a beginner. Any advice would be really appreciated.

Below is a small sample of data:

SupplierCost
=============
SupplierID 1
SupplierCostType E
SupplierCostEffectiveDate 01/01/07
SupplierCostExpiryDate 31/12/07
SupplierCostUnitCost 10

SupplierID 1
SupplierCostType E
SupplierCostEffectiveDate 01/01/08
SupplierCostExpiryDate 31/12/08
SupplierCostUnitCost 20

SupplierID 1
SupplierCostType E
SupplierCostEffectiveDate 01/01/09
SupplierCostExpiryDate 31/12/09
SupplierCostUnitCost 30

SupplierID 1
SupplierCostType B
SupplierCostEffectiveDate 01/01/07
SupplierCostExpiryDate 31/12/07
SupplierCostUnitCost 110

SupplierID 1
SupplierCostType B
SupplierCostEffectiveDate 01/01/08
SupplierCostExpiryDate 31/12/08
SupplierCostUnitCost 120

SupplierID 1
SupplierCostType B
SupplierCostEffectiveDate 01/01/09
SupplierCostExpiryDate 31/12/09
SupplierCostUnitCost 130

PO
==
POID 1
CustomerID 1
SizeType E
Size 3

POID 2
CustomerID 1
SizeType B
sIZE 2

Transaction
===========
TransactionID 1
SupplierID 1
POID 1
OrderDate 09/12/07
Cost 30
CommissionID 1

TransactionID 2
SupplierID 1
POID 1
OrderDate 09/12/08
Cost 60
CommissionID 2

TransactionID 3
SupplierID 1
POID 1
OrderDate 09/12/09
Cost 90
CommissionID 3

TransactionID 4
SupplierID 1
POID 2
OrderDate 09/12/09
Cost 260
CommissionID 4

Commission
==========
CommissionID 1
Commission 6

CommissionID 2
Commission 12

CommissionID 3
Commission 18

CommissionID 4
Commission 52

 

[RiverGuy]

SQL Server is a Client/Server database. This means that it doesn't come with a data entry creation system. So what I think you're really talking about is developing a system, not just a database. The approach you take will determine how complex the database code is. For example, what business rules will you enforce in the database (in stored procedures and constraints for example)? What business rules will you enforce in the application?

The schema you have laid out doesn't look too terribly complicated. But you need to think about how it will all work together. What were you thinking of as using as a development environment to write the application which will generate this data?

 

[bborissov]

Database design looks good at first glance.
You have a normalized data, no double info stored in different tables.
You must set some relations between tables and put some constraints/triggers there for Referential Integrity of the data.

BUT as RiverGuy said SQL Server has no user interface. You should create your own using some other programming language.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[angel00079]

Thanks for the info. I had forgotten about the interface. I think that I am going to concentrate on the back end if I decide to take it on. I have been doing some more reading. Am I right in thinking that I will need a stored procedure to calculate the cost and commission?

I was going to set the keys as follows:

SupplierCost
=============
SupplierID PK
SupplierCostType PK
SupplierCostEffectiveDate PK
SupplierCostExpiryDate
SupplierCostUnitCost

PO
==
POID PK
CustomerID FK
SizeType
Size

Transaction
===========
TransactionID PK
SupplierID FK
POID FK
OrderDate
Cost (derived)
CommissionID FK

Commission
==========
CommissionID PK
Commission (derived)