New Database

[wildgoose1uk]

I am designing a database as a favour for my wife. I haven't used Access a lot for about 6 years when I was using 2003. 2010 looks quite different!



What I need to do is design a database for about 30 contracts my wife's firm holds to provide services in various parts of the UK. The database need to be able to do the following:

• Hold all contract details Site name, address, services provided, terms of contract, when payments expected etc so all info can be outputted into one report either foe all contracts or, more likely, selected contracts;
• Tracking of KPI's on a monthly basis with reports being generated on specific sites and comparative reports across sites;
• The KPI data would hopefully be supplied from the sites and uploaded directly into the database as xls or csv or similar (?xml)

These are the main things to begin with. I have created a table for the contract info with a few other tables for look-up data. The Report Wizard threw a wobbly at the number of fields in the report and refused to do it so I created one manually and just played with it till I got the design I wanted. Still needs tweaking but we are getting there.

Current considerations are:

• Is it best to have everything in one database?
• Or to split them into one table per whole contract including KPI's etc?
• Or One table for the contract details for all 30 contracts and one table for all 30 sites KPI's etc?

For me it is crucial to get this bit right as there are quite a lot of fields so it is quite time consuming. Also I believe it is best to gett he structure right from the start.

Many thanks for your help.

wg

[RayMilhon]

First yes have everything in 1 database. However Look up the term 3rd Normal Form for Your Tables Design. Just from your statement here I would look at a Site Table, Contracts Table, Payments Table. I would bet there should be more but that's all I can get from the data shown.

[rpeare]

The whole purpose of a database is to do exactly what you're asking so, no, you don't want to give each contract a different database or table for that matter.

Without knowing the nature of the contract information you're tracking it's hard to give you any concrete suggestions but think about the rules of normalizing databases. Basically this means that you don't want to store the same information in multiple tables. It's a waste of space and is poor practice. So think about things like, if I have a customer, will they have multiiple contracts? of so I need a customer table and a contracts table. One customer, many contracts (one to may relationship). Will each contract have a list of services provided? If so I need a contract table, a services available table, and a contract services table. One contract many contract services (one to many) and so on.