If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database design help.....
I am in the process of creating a database for a car sales showroom it will
be used to record customer details and there requirements, I have managed so far but I am getting a little stuck and confused with the next part .. Let me explain.... I need to be to set targets for individual sales people and then compare these to there actual sales figures which would then result in a performance figure (Average). Does anyone have any ideas or information which they could share? Any help would be gratefully received. Kind regards Simon |
#2
|
|||
|
|||
"Simonglencross" wrote in message ... I am in the process of creating a database for a car sales showroom it will be used to record customer details and there requirements, I have managed so far but I am getting a little stuck and confused with the next part ... Let me explain.... I need to be to set targets for individual sales people and then compare these to there actual sales figures which would then result in a performance figure (Average). Does anyone have any ideas or information which they could share? Any help would be gratefully received. Kind regards Simon Simon, Well, making *many* assumptions: You would create a SalesTargets Table. It would store information about the sales targets for salespeople. A SalesPeriods Table would be used to store information about the period of time that sales will be examined (daily, weekly, monthly, weekend/promotional, whatever period needed). It would look something like: CREATE TABLE SalesPeriods (SalesPeriodID AUTOINCREMENT ,DateStart DATETIME ,DateEnd DATETIME ,Description TEXT(255) ,CONSTRAINT pk_SalesPeriods PRIMARY KEY (SalesPeriodID) ) CREATE TABLE SalesTargets (SalesPersonID LONG NOT NULL ,SalesPeriodID LONG NOT NULL ,SalesTarget CURRENCY NOT NULL ,CONSTRAINT pk_SalesTargets PRIMARY KEY (SalesPersonID) ,CONSTRAINT fk_SalesTargets_SalesPeriods PRIMARY KEY (SalesPersonID) REFERENCES SalesPeriods (SalesPeriodID) ) Alternatively, SalesPeriods could have it's Primary Key fk'd out to a Promotions Table, and descriptions could be stored there. You would compare the SalesTargets Table amounts via the periods in SalesPeriods to the Sales and/or SalesDetails Tables (or whatever your system uses). These Queries would be used to feed Reports. Sincerely, Chris O. |
#3
|
|||
|
|||
Chris
Thanks for the help but I am a little confused with reagrds to the contraints and references could you elaberate a little for me or if anyone else could help it would be much appreciated. Thanks Simon "Chris2" wrote in message ... "Simonglencross" wrote in message ... I am in the process of creating a database for a car sales showroom it will be used to record customer details and there requirements, I have managed so far but I am getting a little stuck and confused with the next part .. Let me explain.... I need to be to set targets for individual sales people and then compare these to there actual sales figures which would then result in a performance figure (Average). Does anyone have any ideas or information which they could share? Any help would be gratefully received. Kind regards Simon Simon, Well, making *many* assumptions: You would create a SalesTargets Table. It would store information about the sales targets for salespeople. A SalesPeriods Table would be used to store information about the period of time that sales will be examined (daily, weekly, monthly, weekend/promotional, whatever period needed). It would look something like: CREATE TABLE SalesPeriods (SalesPeriodID AUTOINCREMENT ,DateStart DATETIME ,DateEnd DATETIME ,Description TEXT(255) ,CONSTRAINT pk_SalesPeriods PRIMARY KEY (SalesPeriodID) ) CREATE TABLE SalesTargets (SalesPersonID LONG NOT NULL ,SalesPeriodID LONG NOT NULL ,SalesTarget CURRENCY NOT NULL ,CONSTRAINT pk_SalesTargets PRIMARY KEY (SalesPersonID) ,CONSTRAINT fk_SalesTargets_SalesPeriods PRIMARY KEY (SalesPersonID) REFERENCES SalesPeriods (SalesPeriodID) ) Alternatively, SalesPeriods could have it's Primary Key fk'd out to a Promotions Table, and descriptions could be stored there. You would compare the SalesTargets Table amounts via the periods in SalesPeriods to the Sales and/or SalesDetails Tables (or whatever your system uses). These Queries would be used to feed Reports. Sincerely, Chris O. |
#4
|
|||
|
|||
"Simonglencross" wrote in message ... "Chris2" wrote in message ... "Simonglencross" wrote in message ... I am in the process of creating a database for a car sales showroom it will be used to record customer details and there requirements, I have managed so far but I am getting a little stuck and confused with the next part .. Let me explain.... I need to be to set targets for individual sales people and then compare these to there actual sales figures which would then result in a performance figure (Average). Does anyone have any ideas or information which they could share? Any help would be gratefully received. Kind regards Simon Simon, Well, making *many* assumptions: You would create a SalesTargets Table. It would store information about the sales targets for salespeople. A SalesPeriods Table would be used to store information about the period of time that sales will be examined (daily, weekly, monthly, weekend/promotional, whatever period needed). It would look something like: CREATE TABLE SalesPeriods (SalesPeriodID AUTOINCREMENT ,DateStart DATETIME ,DateEnd DATETIME ,Description TEXT(255) ,CONSTRAINT pk_SalesPeriods PRIMARY KEY (SalesPeriodID) ) CREATE TABLE SalesTargets (SalesPersonID LONG NOT NULL ,SalesPeriodID LONG NOT NULL ,SalesTarget CURRENCY NOT NULL ,CONSTRAINT pk_SalesTargets PRIMARY KEY (SalesPersonID) ,CONSTRAINT fk_SalesTargets_SalesPeriods PRIMARY KEY (SalesPersonID) REFERENCES SalesPeriods (SalesPeriodID) ) Alternatively, SalesPeriods could have it's Primary Key fk'd out to a Promotions Table, and descriptions could be stored there. You would compare the SalesTargets Table amounts via the periods in SalesPeriods to the Sales and/or SalesDetails Tables (or whatever your system uses). These Queries would be used to feed Reports. Sincerely, Chris O. Chris Thanks for the help but I am a little confused with reagrds to the contraints and references could you elaberate a little for me or if anyone else could help it would be much appreciated. Thanks Simon Simon, A constraint is the SQL way of creating a Primary Key, Foreign Key. Primary Keys and Foreign Keys can be created by hand via the Relationships Window. There is actually a *major* typo above. SalesTargets should have been: CREATE TABLE SalesTargets (SalesPersonID LONG NOT NULL ,SalesPeriodID LONG NOT NULL ,SalesTarget CURRENCY NOT NULL ,CONSTRAINT pk_SalesTargets PRIMARY KEY (SalesPersonID, SalesPeriodID) ,CONSTRAINT fk_SalesTargets_SalesPersons FOREIGN KEY (SalesPersonID) REFERENCES SalesPersonID (SalesPersonID) ,CONSTRAINT fk_SalesTargets_SalesPeriods FOREIGN KEY (SalesPersonID) REFERENCES SalesPeriods (SalesPeriodID) ) I accidentally typed up two Primary Keys, forgot one Foreign Key entirely, etc. I have now fixed it to show correctly. Data for both SalesPersonID and SalesPeriodID originate in SalesPerson (not shown, but assumed to exist), and SalesPeriods. The Primary Key sets the column that contains the data that uniquely indetifies each row in the table. The Foreign Key is a "reference" back to a Primary Key (usually) in another table (in this case, SalesPersons and SalesPeriods). In this example, if there is a sales person who has a sales target set up in SalesTargets, you cannot delete that sales person from SalesPersons *because* the Foreign Key is set-up. This keeps you from accidentally having sales targets set up for sales persons who don't exist in your system. Or having a sales target for a sales period of unknown length or description (what *was* that 02/19/05 to 02/22/05 period about, anyway?). It's a process named Referential Integrity. Where the "integrity" of the "references" between the tables in the database are protected. Setting these up are a major portion of database design. Sincerely, Chris O. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Free Access Training | Timboo | New Users | 8 | August 17th, 2005 05:58 PM |
I need help with my design Database Requirements.xls (01/01) | Database Design | 2 | December 7th, 2004 02:32 PM | |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |
Form Design with tracking into database | Alicat21 | Worksheet Functions | 1 | June 10th, 2004 12:00 AM |
database design basic help | als0107 | Database Design | 3 | May 6th, 2004 07:26 PM |