Record Storage DB Relationship Help

[MintChipMadness]

Hi Everyone,



I attached a picture of my database relationships. I had to completely change it because I used too many lookups. They seemed easy to use but in the end it made all other tasks much more difficult. Ok so now that I deleted some of the lookups, my database looks like the picture. The lookups between Department, Division, SubDivision, and Category are just for setup purposes for me. The user will only be dealing with the boxes and boxcontents tables. My question is, I need a way to connect the categories table to the boxes table without a lookup. There is information in the category table that is needed but can't be stored in the boxes table. A category would be unique only if I put in a division field in the category table. The unique index in the category table would be division, subdivision, and category combined into one index. I am not sure what to do. Any help would be greatly appreciated.

Thanks,
Eric

Extra Information:
All fields that have ID in the name are numbered fields. The rest are text or date fields.

[jameslarsen]

The main thing I need to know to help you is what is the relationship between the category table and the boxes table? Is it many to many (one category can be applied to multiple boxes, and one box can have multiple categories)? Can each box only have a single category (one to many relationship)?

If it can be many to many, you cannot avoid using a correlative table (named something like Category_Box) that has a key made up of a Category_ID and a Box_ID. For each category that a box has, you would add another record to the Category_Box table, so a box with 5 categories would translate to 5 entries in the correlative table with the same box ID, and 5 different category IDs.

If it is a one to many relationship, you would want to add a non-identifying foreign key of Category_ID to the Boxes table, which could store the single identifier of the category that a specific box has. It would be your choice if you allow null values (a box without a category) or require every box to have a category. Hope this helps, let me know if you need me to clarify one of these solutions depending on what you are trying to accomplish.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[MintChipMadness]

James,
Thank you for your reply. It is a one to many relationship (One Category can have multiple boxes). I created a Category_ID field in my boxes table and will link it with a one-to-many relationship. I have two additional questions. First, how do I populate the Category_ID field with the correct Category_ID information easily? I have 200 records that now have nulls. Second, I am having issues with the Category Table. The subdivisionID (field) + category (field) can be for multiple divisions. The only way to guarantee a unique category is to include the Division ID in the Category table. I have already created a lookup from Division.ID to SubDivision.DivisionID. Should I just include the entire directory (Department, Division, and Subdivision) in the Category table to fix this? I separated them because I didn't want to have to enter the same information over and over again.

Thanks,
Eric

[MintChipMadness]

Please disregard question 2/2 - I figured out that, by the way I connected the tables, the category will be unique.

[jameslarsen]

The most typical way to do this is with composite keys, so the key for the Department table would be Department_ID, then the division table would be both Department_ID and Division_ID, then the SubDivision would be SubDivision_ID and Division_ID and Department_ID. You typically don't want to use auto-generated numbers for IDs unless you have to (meaning there is no other distinct, permanent ID available for a given entity). One thing to consider is how much information are you keeping about each of those entities. For example, if the only information you are storing about the Department is it's name, and it's unlikely to ever change, you could just add a field to the Division table for "Department_Name" and remove the Department table entirely. This won't work if you have additional information you are storing about the department, or may need the name of a given department to ever change, though.

As for populating the Category table, it depends on the values you want in there. If you create an autonumber field and leave it blank when you populate the other fields, the autonumber will automatically populate itself every time a row is added. The best way I usually populate tables that have pre-determined information is to create an Excel sheet with the structure of the table you want, populate it using formulas and copy/pasting, then import it into Access (create an Excel column for the ID field, but leave it blank and it will be auto-populated during the import). If creating this Excel load sheet is not an option for you, give me more information about how the Department, Division, SubDivision, and categories combine and I'll help you with another solution.

Finally, in regards to your solution to create the directory table, that will work if you don't ever need to change any of the names of the departments, divisions, etc. (because those names will be repeated over multiple records, and you never want to have to change a piece of data in more than one place if you can help it). If the Department, Division, and SubDivions names will never change though, you could just add those three fields to the "Category" table and do away with the three tables. Again, I recommend using Excel to create a load table, create the empty table in Access, then import the Excel sheet into the empty table you just created.

[MintChipMadness]

I was able to populate the Category_ID field in the boxes table easily since there was a lot of the same categories. Regarding the Department, Division, SubDivision, and Category tables. I am hoping once they are set, that they won't change. Since this database is in its infancy, I will need to be able to change some of the information. The Department and Division tables won't change but the Subdivision and categories will. I will get those set before importing in the rest of the data. I imported some of the records to test my forms and reports. You probably already noticed that I have fields in the boxes table for Department, Division, Subdivision, and Category. I originally used lookups for these but that made a lot my reports hard to create because the field is the ID number instead of the text. I am doing all my filtering of combo boxes in my forms now so I can have it enter the text in the boxes field. Since I am planning on having the department, division, subdivision, and category fields really go unchanged, except for maybe new subdivisions and categories, that I could use an update query to adjust whatever boxes I already have if someone wants to make a change. I am hoping those requests will be rare once the database is setup. ID field is looking like it is necessary in my case.

I thought that the category would be unique because it is in a relationship with the subdivision ID, which is in relationship with the division ID, which is in a relationship with the Department ID. The subdivision ID will always be unique because of that type of relationship right?

[jameslarsen]

From the design you posted, you only made the "ID" field in each table a part of the key. If you use an auto-number field for the ID, it will always be unique, because any new record that gets added will automatically get a new number, but that in itself won't stop you from adding duplicate information. That's part of the reason people usually use information more closely linked to the actual entity for a key, so that when you try to add a record that already exists, it should send back a key violation. It just means you'll have to be more careful when adding new records, to check for duplicates ahead of time (usually with a query, where any returned records means that what you are about to add isn't unique). Access also allows you to define any field to not allow duplicates, so just add that property to the name fields, and it should help you avoid adding duplicate records by accident.

There is really no reason why you can't keep all the tables you have, so that you maintain the freedom to change the names in the future, as well as add additional attributes for the different entities if you need to in the future. One thing I've learned from my projects is that even small, temporary databases can become large, permanent ones, with new features being requested and added, so thinking about scalability early in the process is never a bad idea.

Also, unless a box can have a department, division, etc. separate from the category it is linked to, I would not include those references in the Boxes table. Link a box to a category, and let the department, division, etc. get linked through the category. Let me know if you need any more help with this.

[MintChipMadness]

I see what you saying about the duplicates. I wanted to be able to put the department division, subdivision and category into one table to make things easier but at this point I can't. Not all the divisions have subdivision and category information yet. I need to maintain what the departments and divisions are to make it easier to remember who to contact to get that information. It will be a small enough directory that there shouldn't be a problem with checking for duplicates.

Regarding the extra information in the boxes table, you are correct. Thank you! I can use a query for a data source on a form and sort combo boxes to get to the correct category. I can figure out that part. The only problem is auto filling in the category ID in the boxes table on my data entry form. The combo box they are using in the sort is the category text field. I added the categoryID information to the query in the form but since the user is not using it on the form it would be weird to have a control on the form with the number. Is there anyway to pull the number from the query and enter it in the Boxes.CategoryID field when the Category ID control in the form is updated?

Thanks again for all your help. I really appreciate it.

Regards,
Eric

[MintChipMadness]

I already ran into a problem on my data entry form. I removed the bound fields for Boxes.Department, Boxes.Division, and Boxes.Subdivision and now the combo boxes are blank when I cycle through the records (not bound anymore). How my form works is I used a query for the form to pull the information and filter by Department, Division, Function, and Category. Those work but it leaves the Department, Division, and subdivision combo box controls blank when I move through the records. What should I do to have the unbound combo boxes know, based on the Category ID chosen, what the directory structure is?

[MintChipMadness]

ok, I made a little bit of progress with my combo box issue. I filtered the query by what is in the CategoryID control of my form (the one I was trying to figure out how to not use) and the query has only one row with all the correct information for the record. I am not sure if I am on the right track though. Some additional information about my setup is all the combo boxes use the same query for the row source and the form is linked to the boxes table.

[jameslarsen]

Not sure if I understood all your questions correctly, but remember that even when binding to a combo box, you can return more than one field (such as the Category_ID, and the Category_Name), but choose to display only the name, then when the user makes their selection based on the name, you can access the ID instead. Unfortunately, I almost always use VBA code instead of binding to populate and update my form controls, so I don't know how much help I'll be with the binding method you're using. Let me know if you're still having trouble and I'll try to help where I can. If you feel comfortable posting your database file, I could also take a look at it to try to be of more help.

[MintChipMadness]

How would I go about binding one combo box like that (to two fields)? So when I filter down to the right Category name it populates Category_Name and Category_ID.

[MintChipMadness]

Since my database design question has been answered, I am going to create a new thread in the form section with my form issues and mark this one solved. Thank you for your help James. If you have an answer to my form issue, I will create the thread right after I finish typing this so I'll come back and edit this with the address to it.


Edit - New thread is https://www.accessforums.net/forms/c...sue-27048.html