Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Unanswered: Please review my Access database

    Hi all, this is my first post. I have created a fictional database which has forms. I would like you guys to review the database and if possible, make any corrections and provide suggestions you see fit. thanks.
    Last edited by sagat4; 02-21-05 at 15:45.

  2. #2
    Join Date
    Dec 2004
    Madison, WI


    One recommendation I might have is instead of storing the Product Number and Branch Number, is maybe just store the actual values rather than the ID's in the customers and Bilda tables. There's nothing wrong with the way you did it, it's the way a typical relational structure would be done. The only reason I would store the actual values verses the ID's is for querying/writing stored procedure purposes - that is, if you wanted to filter/group/query on these values with the source tables, you wouldn't need to link in the lookup/reference table (i.e Products and Branch table). This would make the query/stored procedure run faster if you were dealing with a large amount of data (which you're not though.) Just remember every time you have to link in another table (like a lookup table) into a query means a little bit slower execution time. I'm usually not a fan of having a lookup table with only 2 fields (ID and text field) and storing the ID verses the text value in the source tables but that's not to say that I don't do it that way sometimes.

    But like I said, you don't have hundreds of thousands of records you're querying so this isn't really a factor. If also you were to change the Product Type or Branch Location in the lookup table (or reference table), it would affect all records with that ID. This may or may not be what you would want to do. If you stored the actual values and then say changed the Branch Location name in the Branch table on a record, you would then have to run an update query if you wanted to change that name also in the Bilda table. This again, may or may not be something you would want but something you may want to keep in mind.

    I also noticed that the Branch ID and Product Number in the lookup tables were not autonumber fields (not sure if you did this intentially or not). And the Customer Number field were autonumber fields in both the Bilda and Customer table with a relationship on these fields in the 2 tables. This could be a problem and threw up a big red flag for me but I wasn't quite sure what you were trying to accomplish without getting that deep into the form designs. Usually you don't see a relationship of an autonumber field in one table on an autonumber of that same field in another table which usually leads to problems. Not sure why you did this but you may want to look at it again and maybe make the Customer Number field in one of the tables a number field.

    Also (not to get to critical), don't use "Date" as a field name as this is a key word and may cause you some problems in writing code (I usually use something like DateEntered or DateApproved, etc.) I would also recommend not using spaces in the field names.

    All in all at a quick glance, the forms looked nice - but again, I didn't get that deep into it and mostly looked at the tables and structure.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2005
    thanks for your advice. I will make changes to tables

Posting Permissions

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