Project Correspondence Structure

[silentwolf]

Hi guys,

which way to go about following.

I got a project.
In a project there are several Companies involved. Companies = "Plumber", "Wholesaler", "ProjectTransmitter" (not sure how to call it best in english)
It means that a Company sends us a Project.

Sender is us with different Companies.

Now I like to record correspondence for that project. Either to the Plumer, or the Wholesaler, or the ProjectTransmitter..

Attached a File.
are two different Structures I was hoping someone could give me a answer of which way I should go.
Note: at present there are CustomerID in both tables included but not linked meaning I just left it in there and will delete the ones I wont need anymore
after the structure is correct.

Would be much appreciated if someone could give me an answer to this.

Many Thanks

Albert

 

[Pat Hartman]

Neither looks correct to me.

1. Suppliers and Customers should be the same table, not different tables. That simplifies the correspondance and allows you ONE set of tables/forms to do all correspondance.
2. The correspondence table is a child of Customers/Suppliers
3. Customers/Suppliers has contacts.
4. When you create a new correspondance, you pick the project if that is relevant and you pick the specific contact from the list of contacts for the Customer/Supplier. You might need to cc multiple people so you could make a junction table = CorrespondanceContacts

I don't know what ProjectMembers or Sender are supposed to be.

 

[silentwolf]

Hi Pat,
thanks for your reply!

1. Suppliers and Customers should be the same table, not different tables. That simplifies the correspondance and allows you ONE set of tables/forms to do all correspondance.

So if I would put Suppliers, Customers, in one table but then it would be called (Contacts)
but how to differenciate CustomerNumber from SupplierNumbers and how to use Customers with their Articles and so forth.
Would that not be then also difficult?

Manage Supplier Articles and so on?

But I will look and see if I can create a datamodel as you suggested and see if I can come up with a dabase structure.

I don't know what ProjectMembers or Sender are supposed to be.

Sender:
Is nothing else as different owned businesses.
I can select which business will order or Sell stuff.

Different Companies legal entities. We could choose who is billing or ordering.
For example. One company "ABC Fix Wellness Temples Ltd." another ("BCA Immo Ltd.") Rent Houses, Appartments, Sell Properties.

Project Members:
I just called it like that. Maybe not the best nameing.

However it means who is a project Participant.
In one project there are different companies involed. A Plumber, Wholesaler, and so on..

Now I like to see when I look at the project who is involved with this project.

Cheers again

 

[Pat Hartman]

So if I would put Suppliers, Customers, in one table but then it would be called (Contacts)

No, it would be called "Entities" or "Businesses". You STILL need a contacts table.

I don't know what supplier articles are. If ONLY suppliers can write articles AND you want to enforce that rule, then you can add a Role to the Entities table that defines one as a supplier or a customer, assuming a supplier cannot be a customer. If Entities can have multiple roles, then you need a junction table to tie the Entities to all the roles that are valid.

Can we just talk about one relationship at a time. It will minimize the confusion.

Let's see how the next version of correspondence looks

 

[silentwolf]

Not getting anywhere really.. it drives me insane.

It is part of a bigger database and everytime I like to finish it I come accross problems.
Not sure how you mean it all.. I give up

 

[Pat Hartman]

If designing and developing applications was easy, it would pay minimum wage and not require any particular skills. Divide and conquer as Doc says. you are flitting around instead of digging in to understand one part at a time. Start with the things that are fairly simple and which you understand. Work out and bring in more tables or more functionality. That's why I was trying to get you to pare down the relationship diagram. For purposes of design, you never need to show the lookup tables. Then try to segregate the others by function.

 

[silentwolf]

Well that is all well and good but I need to get this thing done and once I get closer and need to get somthing else into that database all collapses.
And I am getting nowhere.
Ths simpler things are done but that database need more functionallity and when it comes down to that things collaps.

There are customers where we sell our work to there are suppliers where we get material from
There are Projects we are working on and those projects need materials and labour to do the project.

Suppliers can be also customers some of them not many .. however and I like to record things that have been discussed on the project.

Customers can be Private or Companies.

I have made many designs on this already but everytime I like to add something on to that database things seam to be not possible.
Not an easy way of doing it.

I add a small part on how I am trying to get it done but this will also be for the pin I am sure.

Could you be so kind and take a look at it for me please?

So now that would be Contacts where Suppliers and Customers are in one Table.
Correspondence is linked to Contacts as you said and now??

Sender as I said are our companies different companies..

Ok now I have a table called Entities there are Customers and Suppliers in this table.
So a Supplier also can be a customer as a Supplier gives us Projects because he is just supplieng a product to someone.

A bath or a wellness bath or that kind of things.
He does not the repair we do. But if that bath is warranty then he pays for the bill so he becomes the customer.

 

[Pat Hartman]

Ths simpler things are done but that database need more functionallity and when it comes down to that things collaps.

Not that I can see. The schema needs a lot of work before you should start building anything.

If it is easier for you to understand keeping entities separate, then make separate tables. The problem is that once you decide you need to log correspondence, you almost certainly will correspond with all entities and that means either duplicating all the correspondence stuff and having multiple contact tables OR trying to follow my logic and treating all entities as ONE. I've been designing applications for a very long time. I've been down a lot of rabbit holes. I know the pitfalls. When you can't stand back and look at the big picture, you will be constantly blindsided as you seem to be. Schema design is a process. In at some points out at others. I'm offering a shortcut. Feel free to ignore it. Maybe you'll never ever have to correspond with anyone but a customer.

Customers can be Private or Companies

Why does that matter? Are they handled differently? Are there different rules? Once you accept that all "entities" have at least one contact and that contact belongs in the contacts table, does that change how you think about customers? Trying to make one kind of customer (private) that has ONE contact is forcing you to handle the two types differently when they should be handled the same. Once you move ALL contact fields to the Contact table and REMOVE them from the customer table, how does that affect the customer?

Sender as I said are our companies different companies..

In that context - "sender" is just another entity. Again, you might need a Role table if you want to ensure that entity types are identifiable.

A Project is initiated by ONE entity and that FK belongs in the Project table. If you want to include other entities that are doing some task, then add a junction table. I'm not sure what these tasks are yet but whatever.

 

[silentwolf]

It is just a small part of it what I showed here.
And again a new different way of doing it again different lol.

I like to have it simpler as you suggest but not sure how to do it.

So I put all "Companies into one table regardless of suppliers our companies or individuals. Than link a contacts table to that table?

What do you mean by role Table?

I also need billing Invoices and Orders and so on so you work all with just one table for Entities?

How can you have seperate CustomerNr and so on?
And to record who is working on a Project or who is involved in a project?

Maybe you have a small sample of how you do it?

 

[Pat Hartman]

A role in this context is Supplier, Customer, YourCompany. A customer can create a Project but maybe a Supplier and YourCompany can not. Once you take this route, you need to consider the possibility of an entity having multiple roles. YourCompany probably can't be a Supplier but it is conceivable that it could be a Customer if you have a project for your own internal use. It is amazing what problems you can solve that you never even knew that you had.

Once you go this route, EVERYTHING connects to Entity. All relationships are always made using the autonumber of the parent table. NEVER using a different unique identifier although there may be one. If you want some "intelligent" SupplierCD" or "CustomerCD", etc (avoid calling these "intelligent" codes "ID". Leave "ID" to always be an autonumber or the FK to an autonumber.)

Here's a link to a sample that shows how to create two different types of sequence numbers.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

Sorry, I don't have a sample of this type of entity structure.

 

[silentwolf]

So a role table is nothing else then

Company table and Group Table with a junction table CompanyGroup
or is that not correct?

So each company can be one or more of whatever is in the group.
As I had it in my sample or not?

And then where would you link from there?

Thanks for your sample I will look at it! Cheers.

 

[silentwolf]

Ok,

it is 3:30 in the Morning at my end and I did change as Pat suggested at least I hope so .)

Pat is not here anymore but I was hoping that someone could please take a look at it as I need to have it done in the Morning.

Hope I am on the right track now..

Many thanks to all

and again sorry for beeing still working on this!