AbstractGofer said:
Please correct or vertify my application layout to see if I have it lay out
right.
The tables are TblLanguage, TblInterp, TblDispatch, TblCustomer, and
TblClient and the end result of the db is to track customers request of
interpreters. In general to relationships should TblLanguage be the linking
table for TblInterpreter and TblDispatch?
I doubt it, but let's look at this. (Please keep in mind that there are
many ways to organize a database, and other people are likely to differ
from my ideas!) What you suggest here is to have an interpreter link to
a set of languages in which he is proficient. How much information do
you need to record about each such language? Wouldn't its name be
enough? If so, you don't need a separate Table with several fields
describing each language -- a simple field value will be enough, or
maybe you would use a separate Table with only 2 fields: a numeric
primary key, and the name of the language expressed as a text string.
For now, I'll assume you store the name of the language in a field in
[TblLanguage], with no separate primary key.
So far, we have a [TblInterp] Table and a [TblLanguage] Table; each
record in [TblLanguage] specifies a language and an interpreter
proficient in the language; a foreign key to [TblInterp] identifies the
interpreter.
Incidentally, I assume that proficiency in a language is a Boolean
condition (either the interpreter can handle it, or he can't, and we
don't consider being able to translate in only one direction). If this
be not true, you'll also need to include information, probably in
another field in [tblLanguage], about the specific skills the translator
has.
In the table [TblDispatch] we have a list of several (potential) jobs, I
assume, and each involves two languages. Again, each language could be
represented either as a numeric foreign key to a Table listing the
languages, or as a text string naming the language. Even though I'd use
the foreign-key approach, for simplicity here I'll assume you're storing
text strings.
Each record in [TblDispatch] would identify a customer's request for
service; is this true? If so, it would probably need to include fields
for the 2 languages, the customer (a foreign key linking to
[TblCustomers]), the date of the request, and the interpreter (another
foreign key, but left with a Null value until an interpreter is
assigned). I assume that only one interpreter is assigned to each such
job. (If this be not true, you could either allow multiple records in
[TblDispatch] for the same request, one for each translator, or you
could define another linking table that associates several translators
with a job.)
In TblDispatch is were the
transaction occurs and the data will be stored.
Data will probably be stored in all the Tables! For example, if you
hire a new interpreter, you'll append a new record to [TblInterp] and
others to [TblLanguage].
Anyway, there are likely to be other types of data you'll need to store
besides what I mentioned. I suggest that you think about where each
type of datum most reasonably fits. For example, a customer's telephone
number should never appear in [TblInterp]; it belongs in [TblCustomer].
TblCustomer will have a
many-to-many with the TblDispatch and TblClient linking to TblCustomer also.
Reviewing the outline to this db is there an issue for me to address.
I can understand that one customer might contract with you for several
jobs, but I don't see why you need to have several customers share a
single job. What does it mean for them to do this? Do they share the
cost, so that you have to send out separate bills? If this happens only
rarely, you might take care of it by duplicating records, one copy for
customer A and the other for customer B (both records representing the
same interpreting job).
Also, you may be able to combine some of the contact information for
customers and clients in a separate [TblContactInfo] Table -- for that
matter, your interpreters' contact information could also be stuffed
into there. That way, if at times the same person can be either a
client or a customer, you don't have to maintain two copies of his cell
phone number.
===
In looking at your table design, have you considered how you account for
these types of information now? For example, what do you *currently*
know about each client, or about each interpreter? Most or all of that
information probably belongs somewhere in your database.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
