need to create a database and application to record and calculate sale of vehicles, salesperson, etc....?

Question

fake car dealership which sells used cars, theres a service dept, sales team,etc...
need help with design and what the primary and foreign keys are, and the most important thing is how I can tie them all in together, relational database. 10 records per tble is fine and the commission is 10% for each sales person.

guidelines: store info about sales person(company needs flexibility to individually assign a different commission rate
--store info about customer, the vehicles.
--store info needed to calculate profit on a sale(upon which the commission would be based)such as dealer cost of vehicle and selling price
--calculate commission as a % of the profit on a sale.
--show total sales rolled up by month, indiciating totals for dealer cost, selling price, profit, commissions paid.

im so confused and I am not good at this type of stuff. i fell behind in class and now this projects due tonight.

Answer 1

Hi,







database name StoreDB



Tables

1.Tbl_Customer

Fields

pk_int_CustomerID integer(dbtype) auto increment

vaCustomerName varchar(30)

vaContactNo varchar(20)

vaEmailID varchar(20)

vaAddress varchar(500)

fk_int_VehicleID int



2.Tbl_Vehicle

Fields

pk_int_VehicleID integer auto increment

vaVehicleName varchar(50)

vaVehicleDetails varchar(1000)

fk_int_CompanyID int

monPurchasePrice money

monSalesPrice money



3.Tbl_VehicleSalesDetails

pk_int_VehicleSalesDetailsID integer auto increment

fk_int_VehicleID

decProfitPercentage decimal(8,2)

monCommissionPaid money

dateVehicleSaleDate smalldatetime

fk_int_StaffID integer -- sales person



4. Tbl_StaffID

Fields

pk_int_StaffID integer auto increment

vaStaffName varchar(30)

vaDepartmentName varchar(40)

vaContactNo varchar(20)

vaEmailID varchar(20)

vaAddress varchar(500)





and you have to design forms/pages for this





Form1. Manage vehicle (add/edit/delete vehicle details)

Form2. Manage Customer (add/edit/delete customer details)

Form3. Manage Vehicle Sales (in this form we can sale a vehicle to a customer and can set commission to staff)

Form4. Monthly Details (in this form you can get total sales rolled up by month, indiciating totals for dealer cost, selling price, profit, commissions paid.)

Answer 2

Geez I am with colanth, I do this for a living as well, waiting till the last day on a project would put me in the unemployment line.



I am guessing you could find tutorials online to show how to do a DB design.



You have several tables each need to have relationships to other tables,



You have salesperson in this table you would have a join to vehicles, commission rate, and sales,



Calculation would need to go into a paytable of some sort.



The coding required to do a project like this will require some late nights and lots of coffee. A guy could do the rudiments in a few hours but to tweak reports and relations it is a good week or two based on other constraints.



Ask for an extension on the assignment or take your lumps.



Looks as though someone provided a table design, that is a way to start, relationships and testing them are the main issue. relationships between sales, and salesperson and payrate and pay.



Some relationships are one-to-one, others are one-to-many and still others are many-to-one, joins in queries, defining forms.



What is the DB you are using? If it's a Microslop class look at the Northwind Sample in Access.

Answer 3

I do this for a living and I wouldn't try to do it in one day.



Take the hit, ask the teacher who can give you private tutoring and next time start worrying about assignments the day they're given, not the day they're due.