Table/Relationship help

[JDOhio]

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.

 

[dhookom]

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

 

[JDOhio]

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)

 

[dhookom]

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:

[b]Index Name    Field Name     Sort Order[/b]
MyUnique      Fund
              Org
              Acct
              Prog
              Act
              Loc


Primary      No
Unique       Yes
Ignore Nulls No

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

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

 

[JDOhio]

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.

 

[1DMF]

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