Results 1 to 9 of 9
  1. #1
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23

    Database designed around "client name" PK

    You read it right...I designed a big database around a main "Customers" table with a PK that is an indexed (no duplicates) text field containing "Lastname Firstname". At the time, I didn't know it was frowned upon, and I didn't think I could handle working with "blind numbers" holding my tables together. I was teaching myself as I went along, but I needed those customer lists working right away. (I also started with some look-up fields in my tables, but I got rid of those pretty quick before things got complicated.) I do have autonumber fields (ID) in all of my tables, and they are the PK in some of them. When the dreaded duplicate-named client comes along, I was just going to "manipulate" customer names to avoid duplicates (luckily it doesn't violate our business rules) such as using given name vs. "name used", or including a middle initial.



    In any case, other than the potential for a duplicate client name, my database is working very well doing some very fancy billing and time usage calculations, multiple tiered queries, complex forms, etc. I am using cascade update (but not cascade delete) to keep everything matching up. It would be a huge job to switch the database over to an autonumber PK but I'm wondering if I should do something like making a composite key with the ID and the name. The tutorials and explanations of that confuse me a bit. I understand if I did that, I'd have to add the autonumber field in to all of the tables in addition to the existing text name field, but the joins and relationships I'm not sure about.

    Do you think it's worth the effort to figure this out and go to a composite key?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly does this mean?
    a big database
    What is the purpose of the database? How critical is it to your business?
    If you use an autonumber pk, you do not need to make it composite by concatenating more fields.
    Unique is unique.

    What other tables do you have? What relationships between tables?

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02.....

    If you are going to the effort of a composite key, why not just switch to having the PK field being an autonumber?? (with a properly named PK field - not just "ID"!)
    Have you seen this:
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp
    and
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers


    All
    of my tables have an autonumber as the PK field. I don't use cascading updates not deletes - no need!

  4. #4
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    The database is very critical to our business. I do most of the data entry and I use datasheets for most of that (by preference). Staff does little to no data entry, they just run reports and look things up. There are 444 customers in the Customers table, which connects to Billing, Plans, Employment and Notes tables. The Notes table has 127,074 records and includes a memo field so it makes up the bulk of the database and of the data entry (I paste-append the Notes records in from Excel spreadsheets, about 400 a week. I have are 38 tables in all, and they are well normalized by the standards described in the references cited on this and other forums. Total size of the BE is 104,824 KB. Each staff member has a copy of the FE on their own hard drive, about 10,000 KB (over 100 queries plus forms and reports). A partial Relationships diagram is below.

    Click image for larger version. 

Name:	relationships.jpg 
Views:	14 
Size:	133.4 KB 
ID:	25856

  5. #5
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    Quote Originally Posted by ssanfu View Post
    My $0.02.....

    If you are going to the effort of a composite key, why not just switch to having the PK field being an autonumber??
    Because I would be really nervous if the names weren't driving the relationships, and because it would be a lot of work. We had a huge corruption a couple of years ago - primary key designations were lost, autonumber ID's were duplicated. I had to extract everything to Excel and rebuild it, using new ID autonumbers to link things up (Everything linked by ID numbers had to be relinked by looking at the real world data like customer names, etc.) Probably I did something wrong when I extracted the data to Excel, but I was trying to get it out while I could. (The corruption was caused by a server problem so the backups were corrupting when I went into them. I didn't think to move them to a hard drive right away and didn't have good advice at first.) Anyway it made me nervous about numeric-only PK's.

    Edit: I have read those references - that's how I know how it SHOULD be done. I'm just not sure if I should go to all the trouble of changing it.

  6. #6
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    Just noticed that although I'm joining tables on the text (name) field, my PK in Customers is actually the autonumber ID. I could just allow duplicates in the indexed text (name) field to avoid the duplicate customer name problem.

    Feel stupid for even starting the discussion now!!

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are no stupid questions.
    What documentation do you have for the database?
    A description of the business and processes?
    Definition of each table, and field.?

    I think your concerns are misplaced, but it's your data and business. It sounds like there is an issue with keys in relationships. Not sure how you have autonumber PK and yet use a name for joins... maybe I've misunderstood.


    Good luck.

    Do regular backups. Do regular compact and repair.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with orange.

    But I would feel a whole lot better with PK/FK fields that were numeric. I'm just saying...

  9. #9
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    Quote Originally Posted by orange View Post
    Not sure how you have autonumber PK and yet use a name for joins... maybe I've misunderstood.
    Good luck.
    Do regular backups. Do regular compact and repair.
    Thanks Orange. See the relationship diagram above for how I have the PK's and joins. My FK's are an indexed (text) field instead of a numeric that matches the PK.

    TableID (PK)
    TextField (no dups index)---------------> FK (text)

    I am doing backups, C&R, and other than the big corruption event I haven't had any problems getting the data I want. I've never seen anything about that structure that would tell me what the unique risks are of using it. Would be interested in predictions/projections of problems if anyone has any.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-29-2016, 11:56 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 4
    Last Post: 05-16-2015, 10:59 AM
  4. Replies: 4
    Last Post: 01-22-2015, 10:30 AM
  5. Do I need a "client" table?
    By BobDu4 in forum Access
    Replies: 2
    Last Post: 03-27-2014, 08:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums