 | |
#31
 
| |||
| |||
|
Re: Projects database design -
12-23-2008
, 08:29 AM
|
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:
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? |
projects and contacts database in this and other newsgroups over the last
two years.
Some of us participated in those prior dioscussions, and just don't want to
take it on again.
If you go to websites that offer canned solutions (I'm thinking of
databaseanswers.org but there are others), you'll find lots of proposed
solutions. It might be useful for the OP to go study those solutions,
clarify his questions, and then come back here and ask again.
#32
| |||
| |||
|
|
On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
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  Sure we do. And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relate a client to no organization. Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers |
to the project.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#33
| |||
| |||
|
|
On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
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 Sure we do. And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relate a client to no organization. Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers |
to the project.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#34
| |||
| |||
|
|
On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
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 Sure we do. And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relate a client to no organization. Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers |
to the project.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#35
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
#36
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
#37
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
#38
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
(Faint sound of penny dropping in background)
#39
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
(Faint sound of penny dropping in background)
#40
| |||
| |||
|
|
strawberry wrote: On 23 Dec, 00:30, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: strawberry wrote: On Dec 22, 3:30 am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote: Michael Austin wrote: sheldonlg wrote: 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:
personal capacity, while the remaining contacts are connected ina professional capacity. Thinking about it, I guess it's really only 'clients' who createthese 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 Sure we do. *And some of us help others who are trying to help themselves. I know it's how I got started - and I suspect most of the users in this group have similar backgrounds. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== Thanks to all. I think the vagaries of my original post succinctly reflect my state of mind with regard this problem. Once I am able to articulate the problem clearly I suspect that the answer will be self- evident ;-) Here's where my thinking is at the moment: I have a list of Contacts, a list of organizations (Companies), and a list of Projects. Contacts can belong to none, one, or more Companies. All projects have Clients. Clients can be either private individuals (Contacts) or professional organizations (Companies). This means that, on the face of it, there are two ways in which client-contacts can be connected to projects. The question is how best to represent this in my schema? My preferred solution, because I think it will be the simplest to manage, is to insist that clients are always affiliated with an 'organization'. If a client is associated with Project X, say, in a personal capacity then I'll just create a 'placeholder' organization called something like 'Project X: Private Client Body' and add the client contact as a member of that entity. I may try to impose some rules on the naming policy of this organization. This prohibits contacts from being added to the database unless they are members of an 'organization', but allows the term 'organization' to be interpreted quite loosely. The term 'Company' can now simply be discarded altogether. Because it's not entirely intuitive, this solution requires some education on the part of the end-user (but I'm only talking about me and a couple of other guys here); I think a short statement would suffice. Though not perfect, this feels workable so, I'm going to run with it. As always though, any thoughts, criticisms greatly appreciated. Maybe 3 tables - organization, client, organization-client. The third table has three columns: id - auto_increment client_id - from client table org_id - from organization, or NULL if not related to an org This is basically a link table, with one extra feature; you can relatea client to no organization. *Or, have a generic organization "No Organization" for when they aren't associated with any organization. Then the id column for this table would be used in your project table. Not an ideal situation, I admit. *But it might work for you. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== I'll definitely have a linking table, to handle the fact that an individual can belong to more than one organization (and, of course, that an organization will likely have several members) but I don't think I need the surrogate key. I don't think the 'generic organization' idea is a good one though. I think I need to be able to distinguish one 'no organization' from another! Cheers The reason for the surrogate key is to relate a organization/client pair to the project. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck... (AT) attglobal (DOT) net ================== |
(Faint sound of penny dropping in background)
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.