Member Avatar for icccapital

I am trying to build a crm like database with some billing functionality. The way our client base is setup we could have a client that wants to be billed based on a group of client codes and others individually even if they have multiple client codes and still others that are represented only by one client code so they have no group. For those that have a group and use the group it isn't necessary to keep the client codes that are in the group because everything is based on the group. So I am trying to think if have a groups table with a group name and client code column, having nulls for those that don't have groups or have client codes and groups all in one table and have them represent a client and just accept duplicates for those that can be grouped for address and other information, but for billing aren't.

I don't know if that makes sense. Please ask questions to help me clarify. And I appreciate any thoughts you have.

  • 2 Contributors
  • 2 Replies
  • 113 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by icccapital
Member Avatar for debasisdas

If you have designed the DB (something to start with), Please post the ER here. That will be really helpful to add to your ideas.

Member Avatar for icccapital

Well a cross-section of the schema that is already built looks like:

ClientCode(ClientCode VarChar(8), ClientName VarChar(40)....)
Clients(ID Number, ClientCode VarChar(8) References ClientCode(ClientCode), ContactID Number ...)

These are the parts that are the most important I guess. I have initially designed the database so the ClientCode table contains both groups and individual accounts
(these are made up names for the example)
i.e. ClientCode
Group1
Individual2

Where in another system we have group1 contains Individual11 and Individual12. This setup causes the obvious issue that I can't access the individual files in the group, but it is easy to work with because I just bill whatever is in the clientcode table. Now it makes sense to me to have a Groups table and then an Individuals Table and the groups table would have groups and indivudal names.

ie. Groups(GroupName VarChar, IndName, Primary Key(GroupName, IndName))
Individual(IndName References Groups(IndName))

The issue with this for me is that not all individuals will be in the groups table because Individual2 has no group associated with it.

I don't want to write to long of a post so please ask questions about confusing or inadequate information.

Thanks for the help

If you have designed the DB (something to start with), Please post the ER here. That will be really helpful to add to your ideas.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.