Database Structure

[silentwolf]

Hi guys,

this is a new thread but it is following up with my previous thread so not sure if this is ok to start a new one.
But thought it might be better as it changed a bit from the previous

"Datastructure still a little loose" thread​

Not to sure if I was supose to link it ...

However attached is a picture of my current Database Structure.
Could please take someone a look at it as I am still not really sure if that model is correct.

Question Nr:1

Tables:
MatterTyp = Private; Business; Personnel

Matter= MatterTypID=2; Matter= Quote; Invoice; Reminder; Service;

It is a little bit of a mix from DocumentTyp and Subject so I just can't really think of a better way and am not that happy with it.

Background of those tables... If someone calles and I like to "record it" add to table.. then I like to differenciate if it is "Business Related", "Private", "Personnel"
so I can filter it depanding on what selection is made.

Question Nr.2
So there is a bit of reduncence with "MatterTyp" and DocumentTyp what would be a better aproach for this? Different Table all together?

Question Nr.3
"DocumentType" "Quotes", Invoices", "ServiceContract", ...
"Documents" here I add another "Contact" to it as there can be Contact giving me a "Contract" but that Contract is billed to another Customer.

For instance: A Customer named "Huber" send me an "ServiceContract" this I record in Contact_Correspondence.
This ServiceContract shows a "BillingCode" meaning who will be billed for it.
The BillingCode could be "Waranty" so "Huber" is paying for the Service
Or BillingCode is "EndCustomer" therefore "Mr.Burns" will need to pay for it.
Or BillingCode is "DifferentAddress" meaning the Bill goes to "Mr.Burns" but the "Object Address" where that Service needs to be completed is at
a different Address to the billingAddress.

So in the model I think there is a problem with MatterType, and Matter because of redundence Entries.
And I am not sure if that Documents can easily get all information explained above

As Contacts_1 has only Contacts no Addresses.

Hope that is clear what I am explaining but I would be so happy if someone can help me with this as it takes me already far to long to work it out.
And would like to continiou with the database and Forms.

Many thanks

 

[CJ_London]

I've taken a quick look at your relationships and think what you are asking about is buried in there somewhere. Suggest simplify by removing all non relevant tables (the relationships will remain and you can bring them back again at a later date) - i.e. do we need to see all the personnel tables for your question?

Also wonder if some of your tables are actually required - looks like many could be resolved with a value list combo on a form to populate the actual value. e.g. gender, mattertype, discount, methods. Some can simplified by using the 'short' name as the PK such as title, billingcode and units.

 

[silentwolf]

Hi CJ_London,

thanks for your reply! Attached a smaller Portion of the Database. Sorry for showing all tables did not realize that this is not that good as I thougth I guys like to see it.

Also wonder if some of your tables are actually required - looks like many could be resolved with a value list combo on a form to populate the actual value. e.g. gender, mattertype, discount, methods. Some can simplified by using the 'short' name as the PK such as title, billingcode and units.

Well yes I am not sure that is the thing hmm

Keep it simple I guess it is the key to all but can't just get my head around to do so.

Maybe the Attachment can show it better now and you might have some tip for me?

Cheers

 

[arnelgp]

For instance: A Customer named "Huber" send me an "ServiceContract" this I record in Contact_Correspondence.
This ServiceContract shows a "BillingCode" meaning who will be billed for it.
The BillingCode could be "Waranty" so "Huber" is paying for the Service
Or BillingCode is "EndCustomer" therefore "Mr.Burns" will need to pay for it.
Or BillingCode is "DifferentAddress" meaning the Bill goes to "Mr.Burns" but the "Object Address" where that Service needs to be completed is at
a different Address to the billingAddress.

just a thought for you analysis.
is this the actual events?
is there an existing system? follow it and it is already proven.
does all the tables comes from your analysis?
know the business rule.
you can't create a system from your imagination hoping it will be
embraced by everybody, especially those in charge.
your design should be in-line with the current system setup.
you should also talk to those working in the actual environment.
what documents they handle. where it came, where will it go.
look at how they record the documents.
communicate to those involved in data entry , like the one scenario you showed they
know how to handle and separate them.

 

[KadeFoster]

God those pictures scare me

 

[silentwolf]

Hi arnelgp,

thanks for reply!

Not sure if I understand all your questions correctly.

But there is no program at present I can look at or refer to.

I create or like to create this "programm" for a friend of mine. And there is no system beside that one I like to create.

i.e. He works as a contractor for a "Company" but also has his own "Customers" in that case he just creates a "Document" where the ContactID
is the person who gets send the Invoice.

However if he receives a Contract from let's call him "Huber" then there can be those type of situations where he bills either "Huber" or
which ever is on the Contract.

But also with the BillingCode as said could be that "Mr.Burnes" got many objects and on one of those "Object" meaning "ObjectAddress" there is
the service need to take care of.

I can only communicate with my friend and he is not that well with computers in gerneal so I "like" to set it up very easy for him to navigate and entry those "Documents"

And I can not communicate with any other then here lol ..)

Hope that this answers your Questions..

 

[arnelgp]

you cant be expected to create a "perfect" system for the first time.
look at microsoft windows/office itself.
it is still work in progress.
there will always be room for improvement.

 

[silentwolf]

@KadeFoster , hmm why is that so bad what I am doing?

 

[silentwolf]

you cant be expected to create a "perfect" system for the first time.
look at microsoft windows/office itself.
it is still work in progress.
there will always be room for improvement.

well yes I understand that but of course I like to have it as good as possible.
For myself as well as for my friend )

But also it needs to be working and I just can't seam to see it anymore if that it does.
And like to have that sorted as good as possible before I get into creating forms.

 

[CJ_London]

Well yes I am not sure that is the thing

The rules I use for PK's is

1. every table has one - the default being an autonumber
2. do not use multi field PK's - causes issues when required to join to other tables, selecting from listboxes and combos. If you need uniqueness, use a separate index for the purpose. Only exception might be a joining table containing multiple FK's where you are only interested in preserving uniqueness.
3. For lookup type tables, if there is a 'natural' key (e.g. US state CA for California) I would use CA as the PK but see next rule. Other examples would include country codes (as used by windows/google etc) and airports.
4. for string PK's they need to be short (perhaps up to 5 or 6 chars) and zero chance of duplicates (so I wouldn't use an acronym or the first 4 letters of a product type for example - unless the client is prepared to pay for updates!) and have a meaning when seen in a related table as the FK. Lookup tables by definition should not have than many records anyway so the performance overhead of using string PK's in minimal and offset by the fact that it is not always necessary to fetch the data from the lookup table (in the context of the US state - CA is often sufficient).
5. Slightly outside the context of PK's, is to just use a combo based on a value list so no FK required. Applies where there is a) just one column and b) won't ever change - examples might be days of the week, months of the year, perhaps gender or planets in the solar system although that may be debateable these days!

Others may have different rules but the above works for me

With regards your question on redundancy, really depends on how much you are talking about. It is not always right to ensure referential integrity is maintained at all costs.

I know you are keen to move on to forms/reports but before you do, suggest start populating the tables with some example data, ideally in a way that imitates how things happen in real life, see what issues you find.

 

[KadeFoster]

@KadeFoster , hmm why is that so bad what I am doing?

Nah not that, I am kind of an access Noob, and haven't built my relationships yet. So that just looks scary to me atm is all. But its good to see that it can look pretty crazy.

 

[silentwolf]

Hi CJ_London,

many thanks for your Info!!

Just had to find an example on the net to use string PK's never done those befor oops ..)
But makes sence! And yes I will look into to be able to change one or two things with PK's and what ou suggested.

With regards your question on redundancy, really depends on how much you are talking about. It is not always right to ensure referential integrity is maintained at all costs.

Well that would be just for the "MatterTyp" has some of the same Fields as "DocumentTyp"

So I was thinking of somehow perhaps lay it in one table and "call" the "New Table" well an appropiate name.

I know you are keen to move on to forms/reports but before you do, suggest start populating the tables with some example data, ideally in a way that imitates how things happen in real life, see what issues you find.

Yes I will do!

Many thanks It got me going again!

Cheers

 

[silentwolf]

Nah not that, I am kind of an access Noob, and haven't built my relationships yet. So that just looks scary to me atm is all. But its good to see that it can look pretty crazy.

Lol ok yes it can get crazy

 

[CJ_London]

Well that would be just for the "MatterTyp" has some of the same Fields as "DocumentTyp"

personally if you are concerned about two tables with the same fields, I wouldn't worry about it if they are considered to be 'different things'

Bit like suppliers and customers - they both broadly have the same structure but easier to keep them separate

 

[silentwolf]

Oh ok thanks CJ_London.

There remains just a question as I am working through the database again a bit.

On my Contact_Correspondece table I got a FK to ContactPerson.

Is it possible to show in a combobox "cboContactPerson" that it only shows me the relevant ContactPersons of the ContactIDRef?

Is not really casscading so not sure if that is possible or better how.

 

[CJ_London]

not quite sure what you mean because you have a circular ref but suspect if you build your rowsource based around the contacts table will get you what you want

 

[silentwolf]

not quite sure what you mean because you have a circular ref but suspect if you build your rowsource based around the contacts table will get you what you want

Oh so the ContactPersonIDRef is wrong in my Database?
Circular Reference is not good gg

I mean if I would have a form Contact with a sfmContact_Correspondence. If I display ContactID =1 that in a combobox cboContactPerson would show me only ContactPersons belonging to ContactID 1

But when that is a circular Reference then I need to change it anyway.

 

[silentwolf]

This is what I am getting confused by.

First I had the Relationship different .

Contacts, Linked to ContactPerson linked to ContactPerson_Correspondence instead of Contact_Correspondence.

So the Table Contact_Correspondece would be called ContactPerson_Correspondence and has a link to Contact Person

The "flow" would be better but. Because in the Contacts table there are Companies or Private Person stored.

Only the Companies can have ContactPersons well most likely.

So if I have it the oposit like above mentioned then I would need for each "Private" Contact create a dublicate so he is a ContactPerson.

Which is not that great I would imagine.

As below shown in the Attachment. Did not update the Table Name Sorry..

As more I do as less I get it right it seams... (

 

[The_Doc_Man]

I wish to point out a specific case where you will give yourself a TON of trouble. From the image you called "Simple_V1.JPG" I point to the following relationships:

1. A 1/many from Contact to ContactCorrespondence using ContactID to ContactIDRef
2. A 1/many from ContactCorrespondence to Documents using ContactCorrespondenceID to ContactCorrespondenceIDRef
3. A 1/many from Contact to Documents using ContactID and RecipientIDRef
4. A 1/many from Contact to ContactPerson using ContactID and ContactIDRef
5. A 1/many from ContactPerson to ContactCorrespondence using ContactPersonID and ContactPersonIDRef

You will be unable to write an updateable query that would include Contact and Documents in the same query because you have multiple paths to get from one end to the other. This multi-pathing design will ALSO give you entirely too many records since queries have to give you single records based on combinations of things. Multi-pathing doesn't always lead to combinatorial problems - but it is extraordinarily sensitive to doing so. I don't understand all of your intent, but I have to say that the layout in the V1 image WILL cause you headaches.

 

[silentwolf]

Hi The_Doc_Man

I don't understand all of your intent, but I have to say that the layout in the V1 image WILL cause you headaches.

You are absolutly right about that
but how can I change it so it does not give me headaches lol and to be able to move on rather then running in cirles