×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Table/Relationship help

thread700-1740191

Table/Relationship help

Table/Relationship help

(OP)
Hi,

First time poster and I've been scouring the internet for the last week trying to find solutions. I'm fairly new to Access and I'm attempting to build a contract management/transaction database. To start, I've got a screenshot of my tables and current relationships.



Starting at the far right, department and status are present for combo boxes. Status I think I can change to be nothing more than a yes/no field on the Contracts table, though (a contract is either active or inactive).

The Contracts table is exactly that; I don't know I need a sequence field on this table since that's its own table. This table is for the basic information about the contract; who it's with, term, originating department, etc.

The FOAPAL table is our internal accounting descriptor. I don't know if I need to set the primary key as it is, but I wanted to avoid redundant information.

The Sequences table is where I think I start hitting problems. The way the contracts work is each contract can have 1-??? sequences (literally, just sequential identifiers). Each sequence identifies a specific funding source (FOAPAL) for that contract. Every contract will have Sequence 1, so I created a compound key of ContractNum + Sequence for the table. Contract A Sequence 1 is unique, Contract B Sequence 1 can exist and is also unique. Each sequence for each contract will have a specific funding source; it's possible for that to be a repeat, thus the 1:M relationship FOAPAL:Sequences.

Categories feed into the transaction table for expense/revenue.

The Transactions table is the ledger part of the DB and used to record the transactions as it relates to each sequence of a contract.

Are there apparent flaws with the relationship structure and/or table design? If so, what are they and what advice can be offered to resolve them?

Thank you.

RE: Table/Relationship help

I expect the ID fields are unique autonumber fields. I would use these as the primary keys of the tables. I would use these ID field values in the related tables. For instance the FOAPAL.ID value would be stored in the Sequences table rather than 6 field values. All of my primary and foreign keys are single fields.

You can set the six fields in FOAPAL to be a unique index without making them the primary key.

I try to have unique names for every field in my applications. My Contracts table would have the following fields:
conConID autonumber primary key
conContractNum
conSequence
conVendor
conDepartment
conStartDate
--- etc ---

The Sequences table would have:
seqSeqID Autonumber primary key
seqConID relates to Contracts.conConID
seqSequence
seqAmount
seqFOAID relates to FOAPAL.foaFOAID
--- etc ---

Duane
Hook'D on Access
MS Access MVP

RE: Table/Relationship help

(OP)
As someone that's really not familiar with Access and learning as I go, how do I set a unique index for a field that isn't the primary key? That was the main factor for why I set the primary keys as I did. If there's another way to do that, and to set unique combinations (ContractNum+Sequence unique in the Sequence table, for example) I'll go that direction.

Are those the main/only issues you see with the tables and relationships?

I've tried to go further with queries/data entry forms but received some errors. (Which I'm sure I'll be asking about sooner than later, but I've fumbled with the full project for about three weeks and want to make sure my tables and relationships are solid before going further)

RE: Table/Relationship help

When in table design you can open the Index dialog and select multiple fields for an index. Only add an Index Name to the first field and set it to unique:

CODE --> Indexes

Index Name    Field Name     Sort Order
MyUnique      Fund
              Org
              Acct
              Prog
              Act
              Loc


Primary      No
Unique       Yes
Ignore Nulls No

Duane
Hook'D on Access
MS Access MVP

RE: Table/Relationship help

It also looks like you are storing the DepartmentName in the Contracts table. In my opinion, the department ID should be stored in the related table(s).

Duane
Hook'D on Access
MS Access MVP

RE: Table/Relationship help

(OP)
I'll make the changes to the FOAPAL table.

As far as the DepartmentName, you're right and that was an oversight on my part. Form side I'm planning to set it up as a list users can select (based on the table Departments) and wanted to show the relation between the two tables.

RE: Table/Relationship help

I second what Duanne says.

Having finished a diploma course in Database design, here are a few pointer I picked up.

1. Give each table's PK an identifiable name, like you have with DepartmentID.
That way when you look at code or indeed see tables that has an FK, it is obvious which table it references.

E.G. CatgegoryID, SequenceID, ContactID etc...

2. Tables names should really be singular not plural, each record represents a single row (tuple) for a single entity (real world object) for a particular domain (table).

IE. Contract, Department, Sequence, Category etc...

I have so many that break this rule before I knew any better! I know your brain tells you it's plural but academia says differently!

3. Avoid spaces in table names and column names, in some languages CamelCase is considered bad also. I was taught to use all lowercase with underscores as word separators for table names and column names.
If you like CamelCase it's no big deal (I still use it in MS but not Perl/MySQL/PostGreSQL), but do avoid spaces in names, although MS doesn't mind, most other languages / SQL do and it's a pain putting square brackets around things in your code anyway!

Hope this helps.
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close