Fun Database Question!

General Questions
#1

I’m buliding a database at work, and was curious as to what folks think of my design. It’s my first time using access.

Basically, we surveyed employees by having them sort a stack of index cards into six categories of their own creation. We’d like to be able to querry the info by category, by department of the respondees, understanding that there may be multiple people from the same department, and any given item will appear in numerous categories.

I’ve got a few tables:

Department: A key and a list of departments

Testers: A key, First Name, Last Name, and a Number corresponding to the Department

Catagories: A key, the name of the Category, and a Number corresponding to the individual Tester who came up with the category

Objects: A key, the name of the Object (what was written on the index cards), and then n number of Category fields, corresponding to the number of people we have do the sorting. This somehow seems less than efficient, but I’m not thinking of a better way to do it.

#2

That looks pretty good - are your keys automatically generated? That’s the most efficient way to ensure that each record has a unique key. You should also make sure you have relationships between tables, and enforce referential integrity - that’s really what Access excels at (no pun intended :p).

To solve your last problem, it might be better to split Objects into two tables:

  1. Objects, with a key and then the Name
  2. Another table called Objects and Categories, with the following fields:
    a. Key
    b. Object Key (Or ObjectID)
    c. Category

That way, you can use a query to pull up information on particular objects, and you can have an unlimited number of categories without wasting space in the Objects table.

#3

It looks good, assuming I understand your goal here.

Drop the category fields, and add a new table (ObjectCategoryCombinations, or something similar) that has one record corresponding to each object/category combination. What you’re looking at is a many-to-many relationship, and this is the standard approach to representing it in a database.

You can do something similar if you find that multiple testers came up with the same category.

#4

One more vote for an ObjectInCategory table. You pretty much cannot escape it, as the Object —> Category relationship is clearly a Many-to-Many relationship, so you need a Connection Table.

I’m not quite sure about the Categories table – what happens if two different employees come up with the “same” category for a certain object? (As an aside, how do you decide when to categories are “the same”?)

#5

Ok, that makes a lot of sense. I knew there was one bit that I just wasn’t getting. So I’m sure I know what we’re talking about, the ObjectAndCagegory table would look like this?



Key    ObjectID     Category
1        3                 2
2        3                 12
3        4                 1
4        5                 1
5        5                 3
6        5                 4

So that I will have one instance of every Object for each Category associated with it?

ultrafilter and Noone Special, yeah, I hadn’t thought too much about duplicate categories, but I guess I’d do something similar. A table with TestersAndCategories that would have Key, CategoryID and Tester columns.

As for deciding when categories are the same, well, I just run them through my Eonwe’s Brain algorhithm and make the judgement myself. :slight_smile:

#6

Not really. You have one record in the Objects table that corresponds to each object, one record in the Categories table that corresponds to each category, and one record in your third table corresponding to each object/category combination.

#7

That’s exactly what it should look like.

As for grouping categories together, keep in mind whether you need to preserve the original category identifications the testers gave. If you must, then you’ll need to create ANOTHER table, called something like CategoryGroups, having a one-to-many relationship with the Categories table.

#8

Ok. So really I ought to be thinking about each record as its own discrete entity then? There are records of Objects in the Object table, records of Categories in the Cagegory table, and records of Object/Category pairings in the Object/Category table, which contains two pieces of information per record.

It’s not an instance of every Object for each Category it belongs in, it’s really an instance of every Object/Category pairing itself?

In some ways it’s a minor distinction, but I think this is what you mean, yes? It actually makes a lot of sense in this context; I’m just not used to thinking about data in this way.

#9

For technical reasons, I’d rather avoid using the word instance, but you have the basic idea.

#10

Too late to offer more than support to the previous answers. But at least I know there really isn’t a ‘Fun’ Database Question, such a beastie would be rarer than Nessie :slight_smile: