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.
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
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
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
CODE --> Indexes
Duane
Hook'D on Access
MS Access MVP
RE: Table/Relationship help
Duane
Hook'D on Access
MS Access MVP
RE: Table/Relationship help
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
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