A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database design help.....



 
 
Thread Tools Display Modes
  #1  
Old February 6th, 2005, 12:42 PM
Simonglencross
external usenet poster
 
Posts: n/a
Default 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  
Old February 6th, 2005, 05:59 PM
Chris2
external usenet poster
 
Posts: n/a
Default


"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  
Old February 7th, 2005, 04:15 PM
Simonglencross
external usenet poster
 
Posts: n/a
Default

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  
Old February 8th, 2005, 12:36 AM
Chris2
external usenet poster
 
Posts: n/a
Default


"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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.