Thread: Table Design and Primary Key
10-26-12, 19:48 #1Registered User
- Join Date
- Oct 2012
Unanswered: Table Design and Primary Key
First time poster here, so please forgive me (and correct me) if I am not following the appropriate protocol.
I work in accounting and have built quite a few databases in the past, although I dont feel that I've designed them very effeciently. I am starting a new one soon, and I had some questions regarding design, primarily concerning table design and primary keys. Typically I import actuals from the general ledger into my databases (copy and paste from excel actually), and I am specifically questioning the design of the table that holds the actuals. Currently the columns are Year, Month, Cost Center, Account, Expense. Hopefully this makes sense to everyone, but if I need to attach anything to provide better understanding please let me know. I would call this a "vertical" design since the months run vertically. Most databases I've seen have the months running horizontally as columns (i.e. column for Jan, Feb, Mar, etc...). So my two questions are:
1. Is there any reason to build my table with a horizontal design versus a veritcal design?
2. I currently do not have a primary key in this table. Do I need one? If so, why and what should I use? Concatenation of cost center and account?
I guess that was slightly more than 2 questions. Thanks in advance for the help and please let me know if I need to provide further clarification.
10-26-12, 20:37 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
- Yes! Relational Databases are always 'horizontal,' as you call it! In an RD, a 'row' is a Record!
- A Primary Key really has one purpose, and one purpose only, to uniquely identify a Record. Most experienced developers use a Field defined as an Autonumber for this purpose. Access automatically assigns the Value to this Field.
Accounting is not my logn suit, but basically, your 'horizontal' Record should have Fields something like this:
RecordID (the PK, Autonumber)
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
10-27-12, 05:40 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
you can create what you want using a PIVOT view of your data.
or you can stuff the data into a spreadsheet
data is best stored in a database in a normliased form.
it can then be manipuilated and presented in virtually any way you can visualise. if it cannot be done directly on the data, then the data can be exported to analysis tools. Check what supporet your proosed database has for pivot tabels/views etc..I'd rather be riding on the Tiger 800 or the Norton