Dear Kelly:
My available time to be able to post here varies with business needs. I may
not be available on too regular a basis.
Now, to implement the whole thing I expressed is a fair amount of work and
it is expert level stuff. Be sure you up for that. This may not be a good
"first project" approach if you're new to this. But, if you may want to
develop some "lifelong skills" then it may be a way to start.
How many staff and purchasers would you estimate for this database?
In terms of "address" I'm guessing there's no search requirments, just a way
to see, and maybe print addresses. Don't need to find those in specific zip
codes, or cities, or states. What I'm getting at is that the whole address
can be treated as just a "lump" of information not divisible for any likely
foreseeable purpose.
In laying out tables, keep every column name a single word. No spaces or
punctuation. Use notation like this:
LastName
FirstName
As you get more advanced in writing queries, this will be so much more
convenient. There's no drawback to using this kind of convention, and some
advantage.
For a modest size database, I'd recommend dropping the IDs, and put the
whole name in one column, not splitting first and last. Is there any
function where you expect to need to separate them?
When you're assigning a ticket to a purchaser, what do you envision on the
screen? I would thing it would be a combo box of names. This implies every
name must be unique. Otherwise, how will a user pick the right one? If
there are two of them that say John Doe, how would a user pick the right
one? So, the convention needs to be that, when a ticket is sold, and the
name is common, get a middle initial. If the guy is John A Doe and there is
a John A Doe Jr. in the same area, he would know it. Ask! Otherwise, you
end up with persons you cannot distinguish in the database.
If you didn't have a computer, and there were 2 persons named John A Doe,
you would find a way to distinguish one from the other in normal
conversation. People have found such conventions since before there were
computers, and the same conventions work with computers, too.
Now, you can use an ID for them. But how would that help? Do you want John
A Doe # 765 or John A Doe # 814? You can't ask John. How would he know
what number the computer has assigned to him? No, there has to be a
"natural" way of doing it. I cannot tell you what that would be, for sure.
If the 2 John A Doe fellows are not related, but live in the same area, they
may have faced this difficulty before. Maybe they can tell you a "natural"
way to tell which is which. One is bald, the other not. So, one is John A
Doe, Bald, the other Hairy. I don't know. But I do know this. If you're
tryiing to record which bought ticket 2381, just having a combo box list
with 2 John A Doe names doesn't work. Maybe you'll also show the next line
of the address in the combo box. This may help. However, I would recommend
you get something into the single PName (purchaser name) column that
guarantees uniqueness and at least permits and, hopefully, facilitates
proper identification.
For the smaller (I expect) Staff table, the same considerations apply, but
as there are fewer persons there (again, I expect) it's not so difficult.
The above, I'll warn you, is not a "majority opinion" among experts. Maybe
someone will give you another opinion.
For a few thousand tickets, with perhaps an average of several tickets sold
together to one purchaser by the same staff member, you aren't going to have
a huge number of rows of data. I do recommend this will work well without
an autonumber in the tables. Likely, that's not something you've seen
before. We can discuss this further, and cover any reluctance you have.
Maybe enough for now. Let's see how we are communicating so far. Ask me to
repeat anything, or clarify what I have said. Feel free to guide the
conversation into areas that are troubling you most.
Tom Ellison