A normalisation check [back to basics]

[Christopherusly]

The Gurus on this forum have always been great at helping me with the most mundane of questions, one thing i have missed out is the very basics of database normalisation - i always figured since i could get my database to do what i want, i must be doing it right.

Since i have had a verbal spanking from another member about how my database is:

Hmm. Well, not to be a jerk, but your table design is about as non-normalized as I've ever seen.

I thought it would be best to go back to basics. My understanding of Database normalisation was the process of ensuring that the data within a database was efficiently organised and ensuring that there was no redundant / duplicate data - and that the data dependencies made sense.

So onto my database, the starting point is tbl_finance which is imported from an excel spreadsheet (this comes from a SAP business report that my company uses), admitted i have been a little lazy on this one, as the structure exactly matches that of the spreadsheet that SAP coughs up.

I attach for your information and comments a screen shot of my tables and relationships between the tables.

If i have completely missed the point on normalisation please be as free to comment as possible, your comments are appreciated.

thanks

Christophe.

p.s. feeling a bit like the village idiot at the moment.

 

[PNGBill]

Your basic lines look ok whereas some look more like a spiderweb but because some of the tables are not showing all their fields it would be difficult comment much further.

Can you expand the fields and do a new screen shot?

 

[boblarson]

I might suggest just uploading a blank copy of the database (tables only).

Create a blank database file, do an IMPORT of the tables and make sure the IMPORT STRUCTURE ONLY is checked. Then post that.

 

[Christopherusly]

Gents,

Please find attached a blank copy of my database.

Thanks for taking the time to have a look at it.

Christophe.

 

[boblarson]

Okay, opening it up was a bit of a shock at first, given all of the tables that are in there (more tables than are necessary for sure). Some of them look like you are trying to use tables where you could use a query, is that an accurate assessment?

So, what would be helpful, is to explain your business a bit and what the database is meant to accomplish. So, what is it you are wanting to track and what are you expecting to get out of it?

 

[boblarson]

A few observations so far -

1. Your tables ( a lot of them ) have no primary key. And JOB NO seems to be the linking factor in most but it is text instead of numeric. If you have a JOB NO that is alphanumeric then you should probably use an Autonumber for a primary key and store that instead (numeric keys are normally more efficient than text keys and indices).

2. You are storing redundant data in many of the tables. You should not be storing Job Title along with Job NO in each of the child tables. Job Title should only reside in ONE SPOT and then it can be linked via the key to get the name whenever you need to display it.

3. Do NOT use special characters or spaces in field or object names. If you follow this advice, it will be MUCH, MUCH easier when you go to do things with them. For final output, like Reports and all, you can set CAPTIONS for things to be friendly but I would not use captions at table level as they too can cause unwanted side-effects and confusion.

4. For currency fields, instead of using NUMBER / DOUBLE - use CURRENCY. You will find fewer rounding errors and floating point errors if you use the CURRENCY datatype.

More to come later...

 

[Christopherusly]

Sorry its a lengthy post guys, i have tried to keep things as precise as possible.

So, what would be helpful, is to explain your business a bit and what the database is meant to accomplish. So, what is it you are wanting to track and what are you expecting to get out of it?

Once upon a time, my employer used to have a lovely crystal reports system that gave me all the information that i needed, then one day they decided that upgrading to SAP would be the best thing to do and oh, if you want access to the tools you had before, thats 3000£ please, which would never get approved by my contracts manager.

I am a civil engineering project manager who manages a team of 7 engineers, each week time sheets are submitted on a Friday, processed over the weekend and are then live in the system by Tuesday.

Tbl_finance is the structure of the excel report i can tease from SAP on a Tuesday which contains all the information about all projects - and time transactions i.e. the number of time spent per person, by work stage (detailed, preliminary design, or project management time) on each of the jobs.

Some background information:

The common field between the tables is Job No, this takes the format of CO00507230 so its not possible for me to set the field as a number, but is stored as a text field.

I work on several contracts all of which have different charge out rates by grade of staff.

Some projects span more than one financial year and so will need more than one rate,

What i want to get out of the database is as follows:

A report which allows me to query the data between two dates so i can monitor the costs/spend on a job (done) apart from multiple rates spanning two financial periods.

A report which allows me to see what someone has been up to between two dates (done)

A report which gives me a Work IN Progress (WIP) report which takes all the live jobs, calculated the total spent, against the total estimated hours in terms of cost (done)

A report which allows me to calculate utilisation (done)

A report which lets me look at all time movement in the last 30 days (done)

A report which lets me see the hours booked by grade of staff by a selected job, against the estimated hours (done)

there is more, but i think i shall include this in a second post

I knows it a bit of a long post, thanks for taking the time to read.

Christophe.

UPDATE: the first database i posted included all of my temporary tables used in make table queries, i have now tidied this up and it now has the data tables only thank you.