Database design help.....

[Simonglencross]

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

 

[Chris2]

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.

 

[Simonglencross]

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]

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.