/* create my customers table */
CREATE TABLE [dbo].[tbl_cusstomer](
	[customer_id] [int] IDENTITY(1,1) NOT NULL,
	[customer_name] [varchar](max) NOT NULL
) ON [PRIMARY]

/* create my distributors table */

CREATE TABLE [dbo].[tbl_distributor](
	[distributor_id] [int] IDENTITY(1,1) NOT NULL,
	[distributor_name] [varchar](max) NOT NULL
) ON [PRIMARY]

/* my products table */

CREATE TABLE [dbo].[tbl_product](
	[product_id] [int] IDENTITY(1,1) NOT NULL,
	[product_name] [varchar](max) NOT NULL,
	[product_price] [decimal](18, 2) NOT NULL
) ON [PRIMARY]

/* my controlelrs table */
CREATE TABLE [dbo].[tbl_controller](
	[controller_id] [int] IDENTITY(1,1) NOT NULL,
	[controller_name] [varchar](max) NOT NULL
) ON [PRIMARY]

/* my sales table 
this is mostly for payment tracking
note : if full payment set sal_is_outstanding_payment to False
AND set sale_outstanding_id to 0
*/

CREATE TABLE [dbo].[tbl_sale](
	[sale_id] [int] IDENTITY(1,1) NOT NULL,
	[sale_customer_id] [int] NOT NULL,
	[sale_distributor_id] [int] NOT NULL,
	[sale_product_id] [int] NOT NULL,
	[sale_controller_id] [int] NOT NULL,
	[sale_paid] [decimal](18, 2) NOT NULL,
	[sale_date] [datetime] NOT NULL,
	[sale_is_outstanding_payment] [bit] NOT NULL,
	[sale_outstanding_sale_id] [int] NOT NULL
) ON [PRIMARY]

/* from here on I have my basic tables to work my system 
I am still not happy with it...I need to join them in order to view things the way I want...note i am not using foreign keys...I will use Views instead ( they are far more efficient )
*/
/* here is my code for the */
CREATE VIEW vw_viewreport
AS
SELECT     dbo.tbl_sale.sale_paid, dbo.tbl_sale.sale_date, dbo.tbl_sale.sale_is_outstanding_payment, dbo.tbl_sale.slae_outstanding_sale_id, 
                      dbo.tbl_cusstomer.customer_name, dbo.tbl_distributor.distributor_name, dbo.tbl_controller.controller_name, dbo.tbl_product.product_name, 
                      dbo.tbl_product.product_price
FROM         dbo.tbl_sale LEFT OUTER JOIN
                      dbo.tbl_cusstomer ON dbo.tbl_cusstomer.customer_id = dbo.tbl_sale.sale_customer_id LEFT OUTER JOIN
                      dbo.tbl_distributor ON dbo.tbl_distributor.distributor_id = dbo.tbl_sale.sale_distributor_id LEFT OUTER JOIN
                      dbo.tbl_controller ON dbo.tbl_controller.controller_id = dbo.tbl_sale.sale_controller_id LEFT OUTER JOIN
                      dbo.tbl_product ON dbo.tbl_product.product_id = dbo.tbl_sale.sale_product_id

/* now i created this into a view Trust me this bit of magic is far quicker than executing a standard query
*/
/* here are my insert statements */

/* ===================== */
INSERT INTO [tbl_controller]
           ([controller_name])
     VALUES
           ('controller_name')
/* ===================== */
INSERT INTO [tbl_cusstomer]
           ([customer_name])
     VALUES
           ('customer_name')
/* ===================== */
INSERT INTO [tbl_distributor]
           ([distributor_name])
     VALUES
           ('distributor_name')
/* ===================== */
INSERT INTO [tbl_product]
           ([product_name]
           ,[product_price])
     VALUES
           ('product_name'
           ,100.00)
/* ===================== */
INSERT INTO [tbl_sale]
           ([sale_customer_id]
           ,[sale_distributor_id]
           ,[sale_product_id]
           ,[sale_controller_id]
           ,[sale_paid]
           ,[sale_date]
           ,[sale_is_outstanding_payment]
           ,[sale_outstanding_sale_id])
     VALUES
           (1
           ,1
           ,1
           ,1
           ,100.00
           ,02-09-2010
           ,'False'
           ,0)
/* ===================== */

/*
I would do the following : build each insert statement into a stored procedure ( which requires parameters )
I would index my database.
I can then confirm already that by dropping the relationships and doing it this way gives me the following advantages.
1. my database is far more scalable.
2. my database returns data quicker.
3. my queries are simpler and smaller ( sending far less data to the db server )
4. the db server does far less processing 

lets say that eventually I ended up wit h200 000 rows in my database...
all i do is add stored procs in my db that return data in XML format. my system will cache that and work from a disconnected environment and upload only when the user clicks commit changes or on a time-based schedule.
the because I am not using foreign keys It will not change the way I work in code ( i would just change to an xmldatasource almost everything else would stay the same. )
*/