Database design

[Guest]

I am trying to design a database for my photography business. I have, in my
past life, done database design in RBase, DBase, Ingres 4GL, Sybase and
Access. I have, been away from any DB design for a number of years. I'm
trying to do this as quickly as possible, but want to avoid any potential
pitfalls. Is there someone willing to correspond with me privately regarding
this issue, or should I post my (probabably voluminous) inquiries here?

Thanks for your consideration.

 

[BruceM]

You should start by posting your Access questions here. A general
description in non-database terms would be helpful. If you already have
tables, a description of their general purpose and of primary key and other
relevant fields is all that's needed to start.

 

[Guest]

Great! I have my tables designed, for the most part. I can start with a
couple of questions.

First, is there a compact way of display tables and their structure in the
database I have created, in a way that would not be cumbersome to post? I
have 8 tables, at present, with "chained" relationships. (i.e.
Clients->Sessions->Product->OrderMaster->OrderDetail) Or for that matter,
can you point me to a tool that might be able to do a graphical schema
without busting my budget (which is nonexistent)!?

Secondly, how do you force an autonumber column to begin with a particular
number?

And last, for the time being, how do you create keys that use two or more
columns as a combined key?

Thanks.

 

[Guest]

Let me go ahead and quickly outline the tables and their purposes.

CLIENTS- contains account number and basic demographic data (name, address,
etc.)
SESSIONS- contains photographic sessions that I shoot (SessionID, AcctNum,
Start & End date/time fields, 2 classification fields, etc.)

PRODUCT- contains SessionID, Media (i.e. film/digital) and negative/file
cataloguing fields (there may be more than one row for each session as I
often shoot both film and digital but usually have the lab convert my film to
digital at development time)

ORDERMAST - contains master order info such as ProductID, OrderDate, Lab,
Submission and completion dates, delivery dates

ORDERDETAIL - contains detail on individual units ordered by customer
(indicating size, quantity, file/neg references and special instructions. I
haven't yet completely thought out the interrelationships between PRODUCT and
ORDER tables; I'm still working on it.

In addition to these tables, which relate specifically to a finished
product, I have some lookup and detail tables.

FAMDEMO which lists family members and birthdates/ages; CLIENTREL which
details client relationships with one another (I want to be able to know if
an adult child who was part of a family session then becomes a Client
themselves.); CLASSIFICATION which categorizes a job (Portrait broken down
into Family, Indiv, Senior, Pet, etc.;Event->Wedding, Banquet, etc.) with
descriptions.

There's a start!

 

[BruceM]

The way you just posted them is OK. A format that makes it pretty easy to
read is something like this:

tblClients
ClientID (primary key, or PK)
Name, Account number, Address, etc.

tblSessions
SessionID (PK)
ClientID (FK)
StartTime, EndTime, etc.

tblProduct
ProductID (PK)
SessionID (FK)
Media
(Cataloguing field's purpose is unclear to me)

PK and FK fields with the same name illustrate that a client could have
several Sessions, and each session could have several products used.

A few questions:
Is each session related to a client? Can a session involve several clients?

I assume that OrderMast is related to a client. In other words, a client
places an order. If you look up a client, I expect you would want to be
able to see a listing of that client's orders. Do you need to keep track of
Session information in OrderDetails? If you keep track of sessions, you
will also be able to keep track of Products, which are related to sessions.

For the relationships tables, I am a bit uncertain about how to proceed. I
expect you would want a separate table for Children, etc., but I cannot
think offhand of the best way to transfer that information to a record in
the Client table and still maintain the relationship information. I hope
somebody else jumps in on this part of it at least.

In general you will want to create the relationships between the like-named
fields, then build a form (frmClient) based on tblClient, and subforms
(fsubSession and fsubProduct) based on tblSession and tblProduct. You can
drag a subform icon onto a form (fsubProduct onto fsubSession, and
fsubSession onto frmClient) in form design view to create a subform. It
will probably help if fsubProduct is a Continuous form.

That's all I can add for now. I think you're on the right track with the
table relationships. I have used form names, etc. because it is easier to
refer to names than to descriptions, but of course you will be using
whatever names suit you.

 

[Guest]

More questions:

What is the distinction between AllowZeroLength and Required attributes for
a field?

How do I create a date/time input mask?

Each of my primary tables has an ID column which is the primary key on the
table. Is it better to make these columns autonum columns or to assign those
values manually?

Thanks.

 

[Guest]

A few questions:
Is each session related to a client? Can a session involve several clients?

Yes, each session is related to a single client, or so I anticipate.

I assume that OrderMast is related to a client. In other words, a client
places an order. If you look up a client, I expect you would want to be
able to see a listing of that client's orders. Do you need to keep track of
Session information in OrderDetails? If you keep track of sessions, you
will also be able to keep track of Products, which are related to sessions.

That's what I haven't quite figured out. tblProduct is MY output. It would
catalogue the negatives and/or digital files. I'm not sure yet if this is
going to be overkill; My intention is only to catalogue media that meets my
professional standards as a photographer so that when a client requests
prints, I don't have to weed through the unsatisfactory shots to find what I
want. I'm working on a more thorough description of my tables, but may not
have that done for a little while yet.

My initial plan is that a session will be related to a client, an order to
a session, a product to a session but also to a detailed order. That's where
things are still fuzzy for me; I want to avoid creating circular
relationships. Phew, I've been away from this for TOOO long!

For the relationships tables, I am a bit uncertain about how to proceed. I
expect you would want a separate table for Children, etc., but I cannot
think offhand of the best way to transfer that information to a record in
the Client table and still maintain the relationship information. I hope
somebody else jumps in on this part of it at least.

tblFamDemo is primarily a means of keeping track of my clients' family
names and personal info that would help refresh my memory and allow
promotional mailings to be sent. At current it contains only a few fields
but I imagine it expanding as I go along. It's a tool for building client
relationships and marketing.

In general you will want to create the relationships between the like-named
fields, then build a form (frmClient) based on tblClient, and subforms
(fsubSession and fsubProduct) based on tblSession and tblProduct. You can
drag a subform icon onto a form (fsubProduct onto fsubSession, and
fsubSession onto frmClient) in form design view to create a subform. It
will probably help if fsubProduct is a Continuous form.

Yeah, I'm not EVEN there yet. I'm sure I'll have plenty of questions when I
get to that point though.

That's all I can add for now. I think you're on the right track with the
table relationships. I have used form names, etc. because it is easier to
refer to names than to descriptions, but of course you will be using
whatever names suit you.

Thanks for your help.

 

[Tim Ferguson]

Secondly, how do you force an autonumber column to begin with a
particular number?

You don't. An AN's job is to provide a unique number for each row - it's
not a counter, it's not sequential, it's not a rownumber, it's just
unique... nearly always.

If you want a special number to co-incide with some real life allocation
system, them you'll need to program it yourself.

And last, for the time being, how do you create keys that use two or
more columns as a combined key?

Short way: ctrl-click both rows in the table design view and then click
the yellow key (Create PK) toolbar button.

General way: click the lightning-strike toolbar button or choose View -
Indexes to bring up the Indexes designer. In a free row, type a name for
the key in the first column; in the second column enter the first field
in the index; then any other fields immediately below in the following
rows. Adjust the properties of the index in the lower pane when the focus
is on the name-row of the index. You can make a Primary Key or a
candidate unique key or just an access index here.

Hope that helps


Tim F

 

[BruceM]

From Help:

"The AllowZeroLength property works independently of the Required property.
The Required property determines only whether a Null value is valid for the
field. If the AllowZeroLength property is set to Yes, a zero-length string
will be a valid value for the field regardless of the setting of the
Required property."

I have to say I really don't understand the distinction between Null and
zero-length, except that in some cases I believe a zero-length string as the
default value for a field means you don't need to deal with potential Null
values in expressions. I could be completely wrong here.

Date and Time are both stored in all Date/Time fields, and can be displayed
together, but I don't think it's really possible to use an input mask for
date and time in the same field. In two separate fields it is easy enough,
but you may want to consider whether you want to use an input mask at all.
If you format a date/time control as mm/dd/yy, then enter 5/1 and press Tab
(or click outside the field), you will get the full date (including the
current year) in whatever format you choose. If you use an input mask the
user will need to enter all six digits if you want the format to be
05/01/06.

There has been a lot of discussion about autonumber fields. I think that
they are fine as unique identifiers for a field as long as users don't need
to see them. There are some reasonable exceptions to that general rule, but
if users need to see the numbers then I think an automatically incrementing
number could be the best choice. You don't need to choose between
autonumber and manual entry. Is there a particular format you wish to use?

 

[BruceM]

Responses inline.

Yes, each session is related to a single client, or so I anticipate.

I wondered if there could be a many-to-many relationship between Sessions
and Clients. If not, that simplifies things a bit.

That's what I haven't quite figured out. tblProduct is MY output. It
would
catalogue the negatives and/or digital files. I'm not sure yet if this is
going to be overkill; My intention is only to catalogue media that meets
my
professional standards as a photographer so that when a client requests
prints, I don't have to weed through the unsatisfactory shots to find what
I
want. I'm working on a more thorough description of my tables, but may
not
have that done for a little while yet.

If Product is for your information you may not want to include Product
information in Orders (or OrderDetails). A few questions to ask yourself:
Can a single order include output from several sessions? Do you want to
catalogue by Client or by Session? If the former, you may want Product to
be related to Client AND to Session. I have to say, though, that this kind
of relationship pushes the limits of my knowledge. Once you have decided on
what exactly you need to do it would probably be best to start a new thread
(which I will be following with interest).

My initial plan is that a session will be related to a client, an order
to
a session, a product to a session but also to a detailed order. That's
where
things are still fuzzy for me; I want to avoid creating circular
relationships. Phew, I've been away from this for TOOO long!

I would think a Detail may be associated with a Session, but again, I don't
see from what you have said why you would want Product information (as I
understand the contents of tblProduct) on an Order. I could see including
Session information. I may not understand fully, but I'll toss in the
observation anyhow.

tblFamDemo is primarily a means of keeping track of my clients' family
names and personal info that would help refresh my memory and allow
promotional mailings to be sent. At current it contains only a few fields
but I imagine it expanding as I go along. It's a tool for building client
relationships and marketing.

I think it makes sense to have a separate table. You could have a Yes/No
field to use if a child becomes a client in the future, or something like
that.

Yeah, I'm not EVEN there yet. I'm sure I'll have plenty of questions when
I
get to that point though.

Just pointing out the general shape of a possible user interface. I kept in
very general because you are clearly still in the planning stages.

 

[cowartmisc1]

<<FAMDEMO which lists family members and birthdates/ages; CLIENTREL
which details client relationships with one another (I want to be able
to know if an adult child who was part of a family session then becomes
a Client themselves.)>>

Clients can go through 4 stages - they are born, they get married, they
get divorced, and then they die.

I would have a table with 3 fields - birth family number, current
family number, and client number. When 1 or 2 adults enter the system
for the first session, they are both given the same current family
number, but no birth family number. When children enter the system,
they are given a birth family number the same as the parent's current
family number, and they are also given the same current family number
as the parents. If a child grows up and marries, they are assigned a
new current family number. If they divorce, both the parent and the
custodial children receive a new current family number. The birth
family number and client number never change. When a client dies, the
current family number is set to high values.

John Cowart