It seems to me that the model is a little more complex than might appear at
first sight.
1. Firstly you have a Clients entity type which should be modelled by a
Clients table. This table will have columns which represent the attributes
of clients whether or not the client is a company or an individual, but not
those attributes which are specific to one or the other, e.g. it would not
have a CompanyName columns as this is an attribute only of those clients
which are companies; nor would it have FirstName or LastName columns as these
are attributes only of those clients who are individuals.
2. You have a Companies entity type, but as a company is also a Client the
Companies entity type is a sub-type of clients. Sub-types are modelled by a
one-to-one relationship so you'd have a Companies table related one-to-one to
Clients, i.e. the primary key of Companies is also a foreign key referencing
the primary key of clients. This table would have columns such as
CompanyName representing attributes specific to companies.
3. Individuals who are clients are also a sub-type of clients, and thus
modelled in the same way by an IndividualClients table whose primary key is
also a foreign key referencing the primary key of Clients. However, an
individual is not necessarily a client as they might be a shareholder of a
company but not a client per se, so not all individuals are represented in
this table. Consequently this table would not include columns for attributes
which are common to all individuals, e.g. FirstName, LastName, but only those
attributes which are specific to an individual *as a client*. The common
attributes would be represented by columns in a People table, so the
IndividualClients table would include a foreign key column PersonID
referencing the key of people. IndividualClients is not only a sub-type of
Clients therefore, but also a sub-type of People. A table can only have one
primary key of course so unlike ClientID, PersonID is not both a primary and
foreign key of IndividualClients, but as the relationship with People is
again one-to-one the PersonID column in IndividualClients should be indexed
uniquely.
4. As I said in 3 above you'd have a People table with columns representing
Attributes common all individuals such as FirstName and Lastname. Other
contact details would be represented by columns in this table, though you
might need further realted tables if there can be multiple values of the same
type of attribute, e.g. if a person can have multiple addresses and/or phone
numbers.
5. With the basic model established by these four tables, Clients, Companies,
IndividualClients and People (you can of course use table names of your own
choice, but I'd suggest you use terms which describe the entity type modelled
as closely and as simply as possible) you now have to consider how to model
the shareholdings in companies by people. Gina has already given you the
answer to this; its with a another table which models the many-to-many
relationship between People and Companies, CompanyShareHoldings say. This
will have two foreign key columns such as PersonID and CompanyID referencing
the primary keys of People and Companies respectively. Together these two
columns constitute the composite primary key of the CompanyShareHoldings
table. An important thing to understand is that while this table models a
relationship type, its also an entity type as relationship types are really
just a special kind of entity type. So it can have non-key columns of its
own representing the attributes of each person's shareholding in the company,
such as the number of shares held.
You'll probably find the above easier to understand if you draw it out
diagrammatically as an 'entity relationship diagram', with boxes representing
the entity types and lines representing the relationships between them. The
limitations of a text interface make it difficult to do so as a single
diagram here, so I'll split it in two, the Clients and People table appearing
twice whereas on paper they'd appear only once:
Clients-----Companies----<CompanyShareHoldings>----People
Clients-----IndividualClients-----People
The < and > signs in the above represent the 'many' side of each one-to-many
relationship, the lines without these are on-to-one relationships.
Although you have separated companies and individual clients here, the fact
that each is a sub-type of clients means that you have not really done so in
the way you were perhaps thinking of when you said this is something you
wished not to do. Each is still a client entity; all you've done is model
them in such a way as to eliminate inconsistencies in the data which would be
possible if all were in a single table.
With a structure such as that outlined above, which reflects the underlying
reality rather than imposing an artificial framework on it, you should have
little difficulty in creating queries, forms reports etc.
Ken Sheridan
Stafford, England
Thanks for the reply Gina - I have two tables, a master client table which
has all their contact details etc. It includes client companies and
individual clients (I don't want to separate them as an individual (read
shareholder) can be a client in their own right). The second table
(shareholders) has a field that looks up individuals in the client table so
they are linked. The queries I have tried so far have included the Client
company from Client Table and its Shareholders from Shareholder table. But
every combination I have tried has always resulted in contact details for the
Client company rather than for the individual shareholder. I realise the
difficulty of trying to adequately explain for you, but there must be a way I
can choose which query field I want the contact details to belong to. Regards
[quoted text clipped - 26 lines]
--
.
