Database design problem :-(

[jennyek2000]

How could I relate this table in the database?

I have a Jobs table which stores information about engineering jobs and each job has a unique number which is the key. This is linked to a documents table through a one to many relationship, and each job has a number of documents of different types. Each document has a unique reference number which consists of the job number, the document size, the document type and a number which identifes the document within the job. This number depends on the type of the document and the number of that type within the job. E.g. a document which is a sketch has the numbers 1001-1010, with the first document in the job of this type allocated the number 1001:

JOBS(JobNumber (KEY), JobType, Client, Description)

DOCUMENTS (JobNumber (FK), Number, Reference (KEY), DocumentType, Document Size)

In order to determine the Number of the document at any time, I need some sort of number lookup table as follows:

NUMBERLOOKUP (DocumentType, CurrentNumber, NumberLimit)

But I havent a clue how to link this to the database. There should be a record for each document type within each job, Remembering that each job has a number of documents of different types and each type has a specific range of numbers.

SOrry if this is confusing.
Thanks,
Jenny

 

[KenReay]

Hi

You asked "How could I relate this table in the database?"

given the table structure you outline, you can have a one to many relationship between Jobs table and documnets table on JobNumber

I assume you also have 'lookup' tables of Document Types and Documents Sizes ?

is your real question how do you determine the next document number for a 'new' document?

If yes, I would suggest a user Function which takes in the Job Number and Document type of the document and returns next document number.

In a multiuser environment you will need error trapping to guard against two users naking same request at same time and being given same number.

Is this what you are asking?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK