Database Layout

[adams77]

Hello,
I am setting up my first Access database. I have worked with Access for several years but I have never set one up from scratch. I have attached the current layout of the table/fields.

Unit	Contacts	Detail	Dues
UnitID (Primary Key)	AutoNumber	AutoNumber	AutoNumber
Unit	UnitID (Foreign Key)	UnitID (Foreign Key)	UnitID (Foreign Key)
Address1	ContactName	JobClass	Year
Address2	Email	BusinessType	Date
City	Title	ProbPeriod	Increase
State	ContactPhone	InitFee	FT/PT
Zip	Phone	ReinstFee	JobClass
CorpAddr1	Fax	StartAppl	Ded_Mo
CorpAddr2	Interaction	BillingType	StableRate
CorpCity		Frequency	WageBase
CorpState		DuesCalc	Grade
CorpZip			Step
CorpPhone			COLA
New/Merged			ContractExpires
Affiliated			Scale
BA Manager

After looking at the current layout, my questions so far are:

1. Can UnitID (Foreign Key) be used in each table?

2. There are addresses that invoices are mailed to. Some of the addresses are located in the Unit table and others are associated with the contacts? Should I set up a separate table that would contain invoice addresses only? This would duplicate some of the information in the Unit table. Or would I add address information in the contact table? Or do I put all the addresses in one table?

3. Within the Dues table there is a field WageBase. This field will increase each year based on the wage increase. Do I leave this field blank and set up an update query based on the old wage plus the wage increase would equal the new wage?

Thank you for your help!

[adams77]

How do I attach a pdf file?

[Dal Jeanis]

1A. Each Table should have a unique Autokey field.

1B. You can use a Foreign key in any table layout where it makes sense and provides useful information.

1C. Your tables look like they haven't been well thought through, yet. (more later)

2D. Addresses, emails, phone numbers, contacts etc. There are good arguments for putting all the addresses in one table. There are also good arguments for allowing the same address to be in two different tables. The main way to distinguish is usage. How often do these addresses change? Will you often have to update them in two different places? Liewise, how many email addresses can a person have? Phone Numbers? What happens when they change? And is "Contact" a record of the last time you contacted them? If so, separate table, please.

3E. Wagebase. You are much better off setting up a separate table for these wage items, where each new wage record is added with a date begin/date end. If you start by assuming that you need to keep historical data on wages etc, then your table design will be much more normalized, more maintainable, and less likely to get screwed up by banging up against real life.

[adams77]

Thank you for your quick reply!

1C. What should I be asking myself to when designing the tables?

[Dal Jeanis]

See post #5 in this thread https://www.accessforums.net/access/...ess-38592.html

[ssanfu]

Also, be aware of reserved words. "Year" & "Date" are two examples.
See "Problem names and reserved words in Access"
http://allenbrowne.com/AppIssueBadWord.html


Only use letters, numbers and possibly the underscore in object names. NO spaces,punctuation or special characters.