Any Ideas on improving database?? 1

[broodis]

Hi All,

This is basically what my database looks like. It's a bit messy. Just wondering whether anybody could give any advice on how to improve it. Basically what I want to see is who repairs(TechID) the parts(PartsID) and on what date and later link the TechID,PartsID and no.repaired(Repaired) into the tblStore. Thanks.

http://img.photobucket.com/albums/v127/Dremel/diagram.jpg

 

[willir]

broodis

Why the heck do you have the RefDate for the tblStore?

Does this mean you create a new store every time you have a new reference data?? It seems that this is more a daily transaction table. I got to skoot, but I will post later with more info

 

[willir]

Ponder this...

tblStore
StoreId - primary key
StoreName

tblParts
PartID - primary key
PartName

tblTech
TechID - primary name
TechName

tblRepairs
RepairID - primary key
StoreID - foreign key to tblStores
TechID - foreign key to tblTech
NameOfItemtoBeRepaired
ReceiveDate
RepairDate
PickUpDeliveryDate
RepairStatus - text (repaired, unrepairable, inprogress, waitingparts, received, disposed, lost

tblRepairDetails
RepairDetailID - primary key
RepairID - foreign key to tblRepairs
PartID - foreign key to tblParts
PartQuantity

tblStoreDailyReport
ReportID - primary key
StoreID - foreign key to tblStores
ReportDate
BalanceAt8AM
BalanceAt4PM
BadPartsReceived
Unrepaired

I used longer than necessary names to better explain the purpose of the field or table.

I broke out repairs as a one-to-many. If this is wrong, then merge tblRepairs and tblRepairsDetail. Also, I am not sure if you "fix" parts, or use parts to fix something.

The DailyReport is a a one-to-many.

With this design, you should be able to see
- summary statistics for stores
- parts repaired by tech, by part, by date and by store or combination

Richard