Datastructure still a little loose

[silentwolf]

Hi guys,

I am not sure if someone can help me as I did aske in a nother german language forum about some help for Datastructure but I guess it is difficult to understand
what needs to be achieved.
By the way there was only a suggestion linking it to an tbl_Address....

But let me explain.

I started with Contacts as First Object for Data Entry or Search.

tbl_Contact
tbl_Correspondence = "Email", "Phone", "Letter"..
tbl_ContactCorrespondence="In between Table for many to many Relationship"
tbl_CorrespondeceTyp="Quote", "Offer", "Contract"-----basically what Category it belongs to, Contact_1, Offer, Date, and so on..

Now A Contact can have more then one Address, "Billing", "ObjectAdress", "Shipping",
tbl_ContactAddresses

A CorrespondenceType ="Quote","Contract", "Order", a CorrespondenceType kind of a Category of What the Correspondence is or was about
i.E Cont_1, Date, placed an Order....
Cont_200, Date, ask for a Quote... and so on

Now if a Contact places an Contract, I need also where the "Contract", Who placed the "Order" or "Contract", I differ those two in "Contract" meaning where
work needs to be done.

A Order would when something is ordered where we can or must only send those Items

However the biggest issue is
A Contract could look something like that

CompanyA has send a Contract to us
Contract Number, Who is the Customer.. IS NOT THE COMPANY A!! Could be Contact "Hofer", Hofer needs something fixed and that is one of his Rental Properties,
So as Company A sold him this equipment and it is unfortunately already broken.. Mr. Hofer gets it free of charge. And the bill is going to CompanyA.

Some other Time CompanyA Send a Contract, but this time Mr.Hofer made a mistake and orderd the Plumber how sold him that Equipment but this Plumber did not a good job therefore the "Equipment" needs to be fixed in the RentalProperty of Mr.Hofer but he does not like to pay for it as the plumber just finished that job and will take care of the Bill.

So we need to track who was involved in the Contract..
1. Who send us the Contract
2. Who was the Plumber
3. Who is the Customer" AND where does that need to be fixed!"
4. Who will be charged for hat "Contract"
5. Where is the billing Address

And so on .. So ther can be several different "Contacts" be involved for one Contract.
So there could be Company A gives us the contract and has "signature" of the "Client" that this has to be fixed and also from the Plumber so on
We are just getting a preordered Order so to speak that needs to be done.

On the "Contract" we receive from CompanyA there are all the above listed.
Now we need to get that into our System and of course need to be able to get the Address of Billing and the Address of Work easily entered and
further processed for a "Invoice", And other Reports..


Hope this understands someone laugh

Attached a Pic of how the Structure is but not sure how to connect it the best for all the Addresses ..

Thought of Continiouing from Contact_Correspondence but am not to sure ..

Maybe someone had a simmilar issue..
And knows a way of Structure that properly..

Many thanks in Advance

Albert

 

[silentwolf]

here is the Database for better understanding.

Many thanks

Cheers
Albert

 

[Pat Hartman]

If you want to maintain different addresses for one contact, you need two tables. One for the common data and a second for the address. The address subtable would include a type field. However, you need to decide if you are going to duplicate addresses so you have a billing and a shipping address with the same values or if you want to remove the type from the address table and add it as a child so the address row can have multiple types associated with it. It looks like you are using separate contact records if the addresses are different. That will prevent you from viewing email and snailmail from the same contact. The contact also needs a FK to a customer table.

Then you need to decide how to handle email. You could treat it as an address and put it in the address field with a type of email. Structurally that is probably the simplest method. That allows you to treat emails and physical address as the same entity.

 

[silentwolf]

Hi,
Thanks Pat for your reply.
Well I do have at present in the tbl_Contatcs also Adresses which would be the "billing address" of each contact.
And in the tbl_ContactAddresses I have further Addresses of each Contact.

I did name all of my Contacts Contacts regardless if they are Customers or just a Contact which might become Costumer..
I guess that would be enough.. ??

In a different Database I did another Table tbl_ContactOrders linked to Contact Table..
Something down those Lines attached..

 

[silentwolf]

Hi guys,

Sorry for the privious post I did not attach a file but realised it to late!

I did some modification to my Database and think I came up with a reasonable Solution to my "issue" ...
In the DB there are dummy Records but no Forms as yet as I did already make alot of forms for the fishes lol so hope that is ok to just have
a view and see what I am trying to achive here.

Would be much appreciated if someone can take a look at it and perhaps can advice if that would be going along the correct path?

Cheers for your help!

 

[plog]

I looked at the Relationship tool in the database of your last post and here's the issues I see with your structure:

1. Numerated fields. tbl_SupplierArticle has SupA_BezeichnungX where X is a number 1-4, that's wrong. When you feel the need to do that you need to put that data in another table. You use SupA_ID as a foreign key to the new table with 3 fields--1 for the foreign key value, 1 field for the actual value, and then another field to hold the number that is currently in the field name if that number is important.

2. Multiple paths. There are two ways to travel betwen tbl_Contact_Correspondence and tbl_Document_Details--via tbl_CorrespondenceTyp and tbl_ContactDocuments, that's not right. Most likely ContDoc_CorrT_IDRef should not be in tbl_DocumentDetails. Can Document details belong to a quote but its corresponding Correspondence belong to an order? Probably not, but I don't know your data like you do.

3. Redundant fields. tbl_ContactDocuments has a ContDoc_CompletionDate and a ContDoc_IsCompleted field. Wouldn't a completion date signify that its complete? If so, you don't need the IsCompleted field--you just check the date field for a date.

4. Unnecessary table. tbl_Status has 2 values--Active and Inactive. You can accomplish the same thing without a whole table for just 2 possibilities--a Yes/No field. So, tbl_Contacts should not have a Cont_Stat_IDRef field, but a [IsActive] field which is a Yes/No.

5. Incorrectly linked table. You have tbl_Countries and it is directly related to tbl_Contacts, although you don't store any address data in tbl_Contacts you put it in tbl_Contact_Adresses. Shouldn't the country be attached to the address not the contact?

 

[silentwolf]

Hi Plog,

Thanks for your reply!!!

1. Numerated fields. tbl_SupplierArticle has SupA_BezeichnungX where X is a number 1-4, that's wrong. When you feel the need to do that you need to put that data in another table. You use SupA_ID as a foreign key to the new table with 3 fields--1 for the foreign key value, 1 field for the actual value, and then another field to hold the number that is currently in the field name if that number is important.

The reason behind SupA_Bezeichung 1-4 is that we get that data from an external Source a Supplier! And he has those Articles listed like that
it is a rather long Article and each field represent some additinal Infos.
Should I still try to split it up with a FK to the tbl_SupplierArticle?

2. Multiple paths. There are two ways to travel betwen tbl_Contact_Correspondence and tbl_Document_Details--via tbl_CorrespondenceTyp and tbl_ContactDocuments, that's not right. Most likely ContDoc_CorrT_IDRef should not be in tbl_DocumentDetails. Can Document details belong to a quote but its corresponding Correspondence belong to an order? Probably not, but I don't know your data like you do.

I did delete that relationship in the tbl_DokumentDetails.... Did not feel right but was not sure ..

3. Redundant fields. tbl_ContactDocuments has a ContDoc_CompletionDate and a ContDoc_IsCompleted field. Wouldn't a completion date signify that its complete? If so, you don't need the IsCompleted field--you just check the date field for a date.

Done !
4. Unnecessary table. tbl_Status has 2 values--Active and Inactive. You can accomplish the same thing without a whole table for just 2 possibilities--a Yes/No field. So, tbl_Contacts should not have a Cont_Stat_IDRef field, but a [IsActive] field which is a Yes/No.
Done!

5. Incorrectly linked table. You have tbl_Countries and it is directly related to tbl_Contacts, although you don't store any address data in tbl_Contacts you put it in tbl_Contact_Adresses. Shouldn't the country be attached to the address not the contact?

Ok .. I had all Addresses in the Address Table but then thought leaving the Address in the Contact meaning that would be the billing Address.
But it is better for sure to have it all in the tbl_Contact_Addresses The CountryID was in the Contacts as again we get the Data like that from an External Source...

But will change it accordengly )

So would the Datastructure be ok then?

Cheers!

 

[silentwolf]

Hi,
Attached is a update of the Database Structure...
I guess the Tables are well enough named to be able to follow.

I added some additional tables to my structure..

tbl_Document_Members --- found no better name
this table is a LinkTable between tbl_Group(Customer, Supplier, Manufacturer, Plumber)
and tbl_ContactDocuments

Therefore I should be able to get following out of that database
After a Correspondence I enter who contacted me, what was the reference for it was it a Quote, an Invoice, and so on.. When it should be
done by and some Additional Who was ist FirstName Lastname and the Position of that person(Manager, HomeOwner, Supplier,...)

In the Document_Member table I enter information about that one particular ContactDocument, Who is involved in that "Document" and what is his/her group he/she belongs to.. (Client="Billing Address", Client, "Project Address", Plumber=Mr_X, Supplier_Company SuperLux GmbH,

with the tbl_DocumentDetails I am Able to enter all Items for that particular Document.

I guess this would be ok like that or has someone found a "Issue" or some suggestions about a better approach?

Kindly thank you for your help and pactice gg

Albert