Database Structure

[Pat Hartman]

I have to say that I am completely mystified at the business process you are trying to model. I agree with Doc, there shouldn't be multiple ways to get from A to B.

Observation. Unless you are managing huge amounts of data, separating addresses into their own table is probably over kill. You also cause your self the problem of handling addresses for companies and for people. What if a company has multiple addresses and some people are at the "plant" and others are downtown at the "office". It is not wrong by any means to keep addresses in a separate table. It just adds a level of complexity that most small applications aren't prepared to deal with.

Maybe if you give us the 20,000 foot view of what this business does and what you are tracking, we can help you to simplify it.

Part of my issue is your choice for entity names. For example, the heart of the pared down section seems to be "Documents". To most people, that is a piece of paper. So we have a company you are modeling that pushes paper around? You have one person, presumably an employee connected to a document. Does that mean that only that person will ever work on this "document" whatever it is? Are you sure this isn't a m-m which would mean you need a junction table? Then you have documentDetails connected to Articles another term which is commonly used to refer to paper but in your case, I'm guessing you are not talking about printed articles or articles of clothing.

I'm pretty sure the relationship between correspondence and documents is backward unless there is only one piece of paper associated with the main piece of paper. You have used three different terms commonly used for paper - Documents, Correspondence, and Articles and their usage doesn't make sense.

One thing that can help but which is completely unrelated to your question is to use a lookup table management tool like

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

I built the original version in COBOL using IMS over 40 years ago and have implemented something similar in all applications since then. The current version is Access. This is a tool that consolidates all your SIMPLE lookups into a single table and gives you forms and reports to manage them so you don't have to create separate maintenance forms for each lookup table or worse - maintain them by hand.

 

[plog]

Only the Companies can have ContactPersons well most likely.

"Most likely", "Basically", "In most cases", "95% of the time", are all phrases that do not belong in database development. This is a technical pursuit which requires precise, specific and technical definitions.

First let me point out that you have same named fields in multiple tables that are in a relationship with another: Telefon, email, mobile, GenderIDRef, etc.. That's not correct. Most likely those should exist in one table and that one table should be related and referenced when you need that data. The key is to put the data at the lowest level it is needed.

I think the best way to eat this elephant is to open a blank Access database and paste in all your tables without data, just the structures, and build the Relationship Tool. Open the Relationship Tool and add one table to it--whichever one you deem your "main" table. Then choose a table you think relates to that one table and relate it properly. If they have any of the same fields, you must decide which table actually gets to house that field. Update your tables appropriately. Then, one by one, do that for every table--add it to the tool, making sure it relates to only 1 existing table and reconcilling duplicate fields so they exist in only one table. Do that for every table you have and you should end up with a valid Relationship Tool.

 

[GPGeorge]

Most likely", "Basically", "In most cases", "95% of the time", are all phrases that do not belong in database development. This is a technical pursuit which requires precise, specific and technical definitions.

I coined a saying to illustrate that point.

Ambiguity is required in poetry, humor and politics; ambiguity is anathema is relational database applications.

 

[silentwolf]

Wow lots to do and change

Maybe if you give us the 20,000 foot view of what this business does and what you are tracking, we can help you to simplify it.

I want to have it simplified but it is difficult it seams for me to get that correct model in place.

Of course I am not dealing with paper but I thought of keeping it in one place.
A Document could be Invoice, Order, and so on .. so that is why I tried to do it like this and not seperate tables for Orders, Invoices, Correspondence, and so on..
As each should be loged in a why I thought of them as Documents.
For Projects it is the same I like to track how much time is spent on a project.

But it is as you guys said not correct and multiple path of course also not but I just could and still cant work out the easiest and simples way
I rather get further into a big Hole of Tables )

Thanks also for the Link I am looking through this database I am sure it is very helpful!

Many thanks to all other to!

@plog
Will keep on trying to find a why out of this jungle

 

[Pat Hartman]

I think my point was that "paper" was not the business process you are trying to model, Orders, Invoices, Correspondence are all artifacts that don't stand on their own but are part of some larger process such as Order Entry.

Plog's suggestion is probably the simplest technique for clearing the fog from your mind. What you have currently is mush and ambiguity which is why we can't figure it out.

Did you consider actually describing what this business does or do you prefer to keep us in the dark?

 

[silentwolf]

Did you consider actually describing what this business does or do you prefer to keep us in the dark?

Oh no not at all!!

Ok I try to explain!

It is for a friend of mine.

He is selfemployed. He mainly does maintainence work for either his own "Contacts" or "Customers" as well he gets some Contracts
from a "Supplier" or from serval suppliers.

The Supplier is also currently in the Contact Table as I got the data from him as he had it stored. But that is not the issue.

The Supplier provides him with following Information
1. Contact and Address of the Contact who wants this service or maintainance, or repair Service done

2. Is this Contract a "Waranty", or "Billto" Contact in that Service Contract. i.e BillingCode, R1 = Waranty, R2 = Bill to Contact, R3 = BillToOtherAddress ... meaning that the Contact can have a different Billing Address to the Address where this Repair Job needs to take place

If waranty then "Huber" is paying, or is the Contact on that ServiceContract paying for it and so on..

3. What Type of Product is it that needs to be repaired, or maintainanced ...including. Manufacturer, ProductTyp and Product

Now all of that needs somehow be accessable and correct entered and easily entered.
So he is not traveling to the billing Address instead of the address where the repair needs to be done.

After entering all that information he needs to have a plan when he needs to do what and where.
With DrivingRecords and so on but that is not the point... just saying..

And of course then after all is completed he needs to print out "Workhours" Service Report, and of course a Bill.

Hope that is a bit clearer now?

 

[Pat Hartman]

Instead of Contacts, I would name the table Customers. Then ContactPerson becomes CustomerContact. The Customer table should have a BillToAddress. There could be a child table which would be ServiceAddress. So "suppliers" would not have ServiceAddresses unless they also happen to be Customers.

If jobs come either directly from a Customer or indirectly from a Supplier, they are both Customers and I would remove the Supplier table and just keep Customers.

Then you need a Job table. This could be either Contracts or Jobs which ever makes more sense. I can't tell what existing table is masquerading as jobs. I also don't know what kind of details might be involved so I don't know if there needs to be a child table. There probably needs to be something to identify the broken equipment that is being serviced and the material costs. The Job table has a CustomerID so we know who the job is for and a ReferredByID (second FK to Customers) in case a third party is involved. Then the question becomes, when a third party is involved, who is billed, the customer or the third party? Is the answer ALWAYS or sometimes? If it is sometimes, you need a third FK to Customers to hold the BillToID.

Also in the Job/Contract table would be the Service Address so you know where the work is being done.

Then you need a child table that connects the Job to the person assigned to perform the repair. You might also want a Role in case multiple people need to be assigned to the same job. This table should also include the hours worked so that you can later calculate the invoice.

Invoices and Invoice details stand alone. You can't mush them with other "documents". Same for correspondence. Correspondence is normally related to the job but you might also need correspondence related to the Customers. There are ways to combine these into a single table but that doesn't save you any work and complicates the structure so I would keep them separate and "duplicate" the correspondence table if necessary so it becomes JobCorrespondence and ClientCorrespondence.

See if that makes any sense and helps to clarify the murky tables you currently have.

 

[silentwolf]

Hi Pat,

Yes that does make alot of sence and I did made some adjustments and thought about it alot too.

Unfortunatelly I am out for work today so will just need to get back to you with better details a little later.

But sure will give you feetback in more detail once I am back again!

For the meantime I thank you and all others so much for giving me a hand with this!

Cheers!