Re: Projects database design

#1

On Dec 20, 2:37*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

Quote:

My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company * *| Name * * * * | Role * * * * * * * | Tel. * * * *|
+------------+--------------+--------------------+-------------+
* * * * * * *| Joe Bloggs * | Client * * * * * * | 07998765432 |
* * * * * * *| Jill Bloggs *| Client * * * * * * | 02076543210 |
+------------+--------------+--------------------+-------------
+
*+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| John Smith * | Design Coordinator | 07123456789 |
* * * * * * *| Teresa Brown | Site Agent * * * * | 07070707070 |
+------------+--------------+--------------------+-------------+
*+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| Paul Jones * | Electrical Engineer| 07944444444 |
* * * * * * *| Mike Sparks *| Electrician * * * *| 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

#2

strawberry wrote:

Quote:

On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

#3

strawberry wrote:

Quote:

On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

#4

strawberry wrote:

Quote:

On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

#5

sheldonlg wrote:

Quote:

strawberry wrote:
On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

Not to mention that some of us get paid for solving these types of
issues

#6

sheldonlg wrote:

Quote:

strawberry wrote:
On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

Not to mention that some of us get paid for solving these types of
issues

#7

sheldonlg wrote:

Quote:

strawberry wrote:
On Dec 20, 2:37 am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:
My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company    | Name         | Role               | Tel.        |
+------------+--------------+--------------------+-------------+
| Joe Bloggs   | Client             | 07998765432 |
| Jill Bloggs  | Client             | 02076543210 |
+------------+--------------+--------------------+-------------
+
+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
| John Smith   | Design Coordinator | 07123456789 |
| Teresa Brown | Site Agent         | 07070707070 |
+------------+--------------+--------------------+-------------+
+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
| Paul Jones   | Electrical Engineer| 07944444444 |
| Mike Sparks  | Electrician        | 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

Too incoherent. Think about what you want to do and phrase it clearly.
I tried three times before giving up.

Not to mention that some of us get paid for solving these types of
issues

#8

On 21 Dec, 22:47, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

Quote:

On Dec 20, 2:37*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company * *| Name * * * * | Role * * * * * * * | Tel. * * * *|
+------------+--------------+--------------------+-------------+
* * * * * * *| Joe Bloggs * | Client * * * * * * | 07998765432 |
* * * * * * *| Jill Bloggs *| Client * * * * * * | 02076543210 |
+------------+--------------+--------------------+-------------
+
*+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| John Smith * | Design Coordinator | 07123456789 |
* * * * * * *| Teresa Brown | Site Agent * * * * | 07070707070 |
+------------+--------------+--------------------+-------------+
*+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| Paul Jones * | Electrical Engineer| 07944444444 |
* * * * * * *| Mike Sparks *| Electrician * * * *| 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

I have to admit that I stopped reading when I realised that I couldn't
understand why the statement: "My projects database has a table of
projects and a table of contacts. This creates redundancy because
several contacts can be affiliated with the same company.", had to be
true, especially when I saw "Obviously, I need to create a company
table that holds company
information common to all individuals..." and realised that this was
not at all obvious to me.

The problem with trying to help you is that is that you are presenting
us with just some of the distinct difficulties that you have hit in
implementing your macro design, rather than explaining the overall
intentions of the application and so things that are obvious to you
are not so to us.

When designing a database schema for an application, I start from the
beginning and build it up bit by bit, something like writing a novel I
guess. So I can't necessarily see the answer because I don't know all
the background. For example, I can see no reason why, if you want
contacts at the centre of your application, you can't just have some
many to many tables which link contacts to all the entities of which
they can be members. But maybe this doesn't fit with the overall
design and so it is better to treat a two contacts who are the same
person as distinct entities depending on whether they are affiliated
with a certain company or not.

It looks like you are dealing with the same sort of problems that CRMs
have to contend with, being very "contact" focussed. It might be an
idea to take a look at the schema for something like SugarCRM.

I know you have seen often in this forum folks who ask for an answer
to a question and, after much probing, we discover that the questions
shouldn't need to be asked because they are using completely the wrong
design. These posts usually contain the phrase "I need to..." (as in
"I need to select one item from a list in a single field" when really
they need to normalise their data). I'm not saying that your design is
wrong, only that, as in writing a novel, you are at chapter 12 and are
asking someone who has no knowledge of the other 11 chapters or any of
the characters, whether Jim should be wearing blue or green trousers.

#9

On 21 Dec, 22:47, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

Quote:

On Dec 20, 2:37*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company * *| Name * * * * | Role * * * * * * * | Tel. * * * *|
+------------+--------------+--------------------+-------------+
* * * * * * *| Joe Bloggs * | Client * * * * * * | 07998765432 |
* * * * * * *| Jill Bloggs *| Client * * * * * * | 02076543210 |
+------------+--------------+--------------------+-------------
+
*+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| John Smith * | Design Coordinator | 07123456789 |
* * * * * * *| Teresa Brown | Site Agent * * * * | 07070707070 |
+------------+--------------+--------------------+-------------+
*+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| Paul Jones * | Electrical Engineer| 07944444444 |
* * * * * * *| Mike Sparks *| Electrician * * * *| 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

I have to admit that I stopped reading when I realised that I couldn't
understand why the statement: "My projects database has a table of
projects and a table of contacts. This creates redundancy because
several contacts can be affiliated with the same company.", had to be
true, especially when I saw "Obviously, I need to create a company
table that holds company
information common to all individuals..." and realised that this was
not at all obvious to me.

The problem with trying to help you is that is that you are presenting
us with just some of the distinct difficulties that you have hit in
implementing your macro design, rather than explaining the overall
intentions of the application and so things that are obvious to you
are not so to us.

When designing a database schema for an application, I start from the
beginning and build it up bit by bit, something like writing a novel I
guess. So I can't necessarily see the answer because I don't know all
the background. For example, I can see no reason why, if you want
contacts at the centre of your application, you can't just have some
many to many tables which link contacts to all the entities of which
they can be members. But maybe this doesn't fit with the overall
design and so it is better to treat a two contacts who are the same
person as distinct entities depending on whether they are affiliated
with a certain company or not.

It looks like you are dealing with the same sort of problems that CRMs
have to contend with, being very "contact" focussed. It might be an
idea to take a look at the schema for something like SugarCRM.

I know you have seen often in this forum folks who ask for an answer
to a question and, after much probing, we discover that the questions
shouldn't need to be asked because they are using completely the wrong
design. These posts usually contain the phrase "I need to..." (as in
"I need to select one item from a list in a single field" when really
they need to normalise their data). I'm not saying that your design is
wrong, only that, as in writing a novel, you are at chapter 12 and are
asking someone who has no knowledge of the other 11 chapters or any of
the characters, whether Jim should be wearing blue or green trousers.

#10

On 21 Dec, 22:47, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

Quote:

On Dec 20, 2:37*am, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

My projects database has a table of projects and a table of contacts.
This creates redundancy because several contacts can be affiliated
with the same company. Situations also arise where a contact is
affiliated to more than one company (the director, say). And sometimes
contacts are both clients of one project and contractors of another.
Clients, especially, can also be private individuals, or acting as
such and so not connected with a project in any 'professional'
capacity. Finally, a client may choose to affiliate themselves with a
project via the vehicle of a company, but this fact may not be known
at the outset.

Obviously, I need to create a company table that holds company
information common to all individuals associated with that company (or
that branch of the company), but then I'm unsure how to manage all
these complexities.

In the end, I'd like to be able to select a project and see a summary
list of contacts associated with it that I think would look something
like this (sorry, my view of this forum doesn't seem to support
bbcode, but maybe yours does):

Code:

+------------+--------------+--------------------+-------------+
| Company * *| Name * * * * | Role * * * * * * * | Tel. * * * *|
+------------+--------------+--------------------+-------------+
* * * * * * *| Joe Bloggs * | Client * * * * * * | 07998765432 |
* * * * * * *| Jill Bloggs *| Client * * * * * * | 02076543210 |
+------------+--------------+--------------------+-------------
+
*+Builditkwik (Main Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| John Smith * | Design Coordinator | 07123456789 |
* * * * * * *| Teresa Brown | Site Agent * * * * | 07070707070 |
+------------+--------------+--------------------+-------------+
*+Tee and Biscuits (Electrical Sub-Contractor)
+------------+--------------+--------------------+-------------
+
* * * * * * *| Paul Jones * | Electrical Engineer| 07944444444 |
* * * * * * *| Mike Sparks *| Electrician * * * *| 07944444443 |
+------------+--------------+--------------------+-------------+

Here we have two contacts who are affiliated with a project in a
personal capacity, while the remaining contacts are connected in a
professional capacity.

Thinking about it, I guess it's really only 'clients' who create these
complexities, so perhaps (where the client is acting in a private
capacity) I could just store the client's contact_id in the projects
table itself. Then, when dealing with a professional client, this
value could just be NULL, and then there could be two further tables -
one which records which companies are affiliated with which project
(and in what capacity), and one which identifies the specific
employees associated with that project.

Sorry for rambling. I'm just thinking out loud. Anyway, I hope I've
explained clearly enough and I'd be grateful for any thoughts you
might have on managing these complexities.

No takers? Too simple? Too difficult? Too obscure/incoherent?

I have to admit that I stopped reading when I realised that I couldn't
understand why the statement: "My projects database has a table of
projects and a table of contacts. This creates redundancy because
several contacts can be affiliated with the same company.", had to be
true, especially when I saw "Obviously, I need to create a company
table that holds company
information common to all individuals..." and realised that this was
not at all obvious to me.

The problem with trying to help you is that is that you are presenting
us with just some of the distinct difficulties that you have hit in
implementing your macro design, rather than explaining the overall
intentions of the application and so things that are obvious to you
are not so to us.

When designing a database schema for an application, I start from the
beginning and build it up bit by bit, something like writing a novel I
guess. So I can't necessarily see the answer because I don't know all
the background. For example, I can see no reason why, if you want
contacts at the centre of your application, you can't just have some
many to many tables which link contacts to all the entities of which
they can be members. But maybe this doesn't fit with the overall
design and so it is better to treat a two contacts who are the same
person as distinct entities depending on whether they are affiliated
with a certain company or not.

It looks like you are dealing with the same sort of problems that CRMs
have to contend with, being very "contact" focussed. It might be an
idea to take a look at the schema for something like SugarCRM.

I know you have seen often in this forum folks who ask for an answer
to a question and, after much probing, we discover that the questions
shouldn't need to be asked because they are using completely the wrong
design. These posts usually contain the phrase "I need to..." (as in
"I need to select one item from a list in a single field" when really
they need to normalise their data). I'm not saying that your design is
wrong, only that, as in writing a novel, you are at chapter 12 and are
asking someone who has no knowledge of the other 11 chapters or any of
the characters, whether Jim should be wearing blue or green trousers.