Advice on Table Structure 1

[danneedham]

Hi All

I am currently in the early stages of creating a sales database for work.

I was planning on the main sales table to be as follows:

¦ Date ¦ StoreID ¦ Amount ¦

The main queries from this data will be on "Week No" contained within another table, or "Period No"...

Am i best to have these in seperate tables, or to store the week and period number within the sales table...

Just very concious of having to pull all the data down, to then query and get my results, if the table already contained the week number and period number would this run quicker?

It runs fine now, but very concious at the end of year one it will hold around 93,500 records...

Looking forward to your replies!

Dan

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[danneedham]

Thanks PH

 

[danneedham]

Further advice??

Think from the above, i should hold just the actual dates in my sales table...

now, i need a table to hold my financial dates... Within a financial year we have periods (the months) and then the weeks within them....

I would like the users to query either by period, or by weeks...

I did have this as my initial table:

¦ F.Year ¦ Period ¦ Week ¦ Week Commencing ¦
2008_09 01 01 27/01/08
2008_09 01 02 03/02/08
2008_09 01 03 10/02/08
2008_09 01 04 17/02/08
2008_09 02 05

and so on... please help!

Dan

 

[fneily]

Probably help us more if you showed your table structures, such as:
tblSale
SalesID Primary Key
Date
StoreID
Amount

Your tables should have a primary keys so your financial table should look like:
tblFinancialPeriods
FPID Primary Key
F.Year
Period
WeekNo.
StartDate
EndDate

But you don't really need this table. Look at Pivot Tables.