Basic table creation

[Kanadka]

Just learning and I have a question about creating tables. For fields that have set values, what criteria do you to determine whether or not to use a combo box or a new table? For example, if you have an Employee table and one of your fields is Roles, would you also have a table for the types of roles, then just have the Roles field in the Employee table point to the Role table or would you just create a combo? As I'm designing my tables, I'm discovering that several of the fields could have set values, and I don't want to go too crazy with the table creations.

[June7]

Either way, need a combobox or listbox on form. If the variety of roles are few and will never change, can use a value list instead of table as source of list. If you use value list and the roles change, this means design edits instead of just editing records.

A table for something like Male/Female seems like overkill to me but I have seen it.

[Kanadka]

I should mention that the roles probably won't change, but an employee can have multiple roles, such as Developer and Team Lead. So maybe I just answered my own question since I read that you should not have repeating data, such as Role1 and Role2.

[Kanadka]

I'm not so sure why this is so confusing to me, it seems so simple, but I am having a major mental block as to how this works. In keep with the thought that you should not have repeating fields, if an employee can have multiple roles, would I have two records in the Employee table for that employee?

EmployeeTable:

EmployeeID
FirstName
HireDate
IsActive
LastName
...etc
Role (from the RoleTable)

RoleTable:
RoleID
RoleName

[June7]

You need a junction table.

EmpRoles
EmpID
RoleID

The alternatives to junction table:

1. multi-value fields (I NEVER use)

2. multiple Role fields in EmployeeTable (which you already correctly identified as a design flaw but can be dealt with)

[Kanadka]

Thanks. I'm trying to look at some database examples, but it's hard to get the basics from the larger databases. I see a lot of these samples have tables appended with 'detail', my guess is that they are the tables bringing the information together. I will take a peek at those.

Thanks again.

[June7]

A 'detail' table is often seen in situations like purchasing. A purchase can involve many products. So there is a table for the general purchase order info (ID, date, vendor) then a related child table that lists the products associated with the order - this is the junction table. It associates the order with records from the Products table.

[Kanadka]

Thank you. That seems to have worked. The only issues I have now is with my form - see attachment. In my form, I can fill out all the employee information and then also add their roles, but in that section, it just lists the RoleID. How can I get it to list the RoleNames instead, so I don't have to memorize the ID for each role? I've attached a couple screenshots, if that helps.

[June7]

Use a multi-column combobox. Review: http://www.datapigtechnologies.com/f...combobox3.html