Normalization question (1 Viewer)

[Holmes]

Ok I've searched through and read about normalization can someone just have a quick once over my grasp of this concept.

I am designing a database for service recrods, the database will detail Customer contact details, service records, location and instruments.

so I have 4 tables:

1 listing locations and adress containing the postcode.I have set the postcode to the PK.(tblLocations)

tblCustomer listing customer details, contact number name including the postcode again as there may be 2 people working at one site but in different departments.

tblInstrument listing the instruments that need servicing this table has serial numbers for all the instruments and also contains postcodes. However the pk is the serial number.

The final table is the service history table tblService. This lists what services were carreid out at which site. It inculdes Location, Contact, Postcode, Instrument, work carried out and serial number and serviceID. Service ID is set to the primary key.

The tblService pulls info from the other tables so that all that is entered is the problem.

So I have the hospital table linked to the customers via Postcode. The instruments are linked to the hospital again via postcode. The service records are then linked to the location using the ServiceID field.

As one location can have many instruments, service calls, contacts I have made all links one to many from the hospital to all.

Have I got the correct idea?

many thanks.

 

[ColinEssex]

why use the postcode as the link? thats very unstable. Postcodes change, people move, people type stuff in wrong etc etc

Use an AutoNumber, thats what its for. Have the AutoNumber generated in your customer table, then link everything to that.

Col

 

[Holmes]

Ok, fair point.

 

[Holmes]

Hit post too quickly, is there anyway to change the auto number?

Rather than using 0001 etc. Can I preced the autonumber with REF00xxx etc
or somethign similar?

I guess I could do this by using the input mask?

Cheers.

 

[ColinEssex]

Don't do anything with the AutoNumber just leave it, you can even hide it on the form so it won't confuse things.

Have a separate number using DMax() with your prefixes

Col

 

[Holmes]

using DMax() with prefixes, you've totally lost me there!

 

[The_Doc_Man]

The thing about autonumbers is that they are guaranteed to be unique but NOT to be monotonically increasing. If you need to change the autonumber, you are doing something wrong and misunderstand its purpose.

All those other numbers are identification data that could change over time, things that might need editing. But to have a meaningful data relationship, you must have one immutable key. That is why autonumber is what it is. You DO NOT CHANGE it. Anything that could change over time is NOT a candidate for prime (standalone) key. This is a fine point about normalization.

 

[Moniker]

DMax just returns the highest value from a list of values (which is what an AutoNumber column is).

The change the format to "REF0001" or anything else, just place it in the Format property in table design view. The format for what you want is this:

Format = "REF"0000

This would generate REF0001, then REF0002, and so on.

Additionally, if, for example, you wanted REF# 0001, the format would be:

"REF# "0000

The formatting can be anything here really. The AutoNumber will automatically increment the numeric part of the format, so in the case of an AutoNumber, your format must include some numeric portion.

~Moniker