Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    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.

    Matt

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by mattk884 View Post

    Most databases I've seen have the months running horizontally as columns (i.e. column for Jan, Feb, Mar, etc...)
    Have no idea where you've seen these 'databases,' but you're describing a spreadsheet, not a Relational Database!
    Quote Originally Posted by mattk884 View Post

    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?
    1. Yes! Relational Databases are always 'horizontal,' as you call it! In an RD, a 'row' is a Record!
    2. 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)
    Year
    Month
    Cost Center
    Account
    Expense

    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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •