Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    48

    Question Unanswered: Checkbox and Tables

    I am working on a project in Access and wanted to know if this was considered "poor normalization".

    I have a employee database and in main table I have basic customer info.
    empID (pk)
    LName
    FName
    Address,
    LastInput
    etc.

    I have a serious of as of right now 18 fields in that table that pertain to employee info. I

    I have a series of checkboxes probably a total of 24 or 30. checkboxes. Each checkbox located in a section A, Section B:

    I wanted to know if it would be incorrect for me to insert the checkbox fields into the Main table or would I be better off creating seperate tables for each section. Keep, in mind that not some employees may not even be checked. Meaning, some emps may have no selected fields in Section A but Section B may have 3 check boxes marked.

    I have 22 tables already but some may be eliminated.

    Any help would be appreciated.

    Thank you in advance,
    ron b

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    The question you need to ask is: Does these checkboxes DESCRIBE the employee? At 1st blush, I'd say no ... Put that stufff into a separate table (or tables) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Posts
    48
    Thanks for your input, just one more thing.

    Should I join this checkbox table with the Main table by EmpID --> Checknumber

    MainTable
    EmpId (PK) text

    CheckBoxTable
    checknumber (PK) text

    I am thinking that this is a 1:1 relationship but some Emps may not even need to be checked.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Ronbegone
    Thanks for your input, just one more thing.

    Should I join this checkbox table with the Main table by EmpID --> Checknumber

    MainTable
    EmpId (PK) text

    CheckBoxTable
    checknumber (PK) text

    I am thinking that this is a 1:1 relationship but some Emps may not even need to be checked.
    How do you relate an employee ID to a checknumber ID? You can't ... You don't relate columns to one another, you relate TABLES. So, the process of relation is to have COMMON columns between tables (i.e. a key field and in the related table a FOREIGN key) ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2004
    Posts
    48
    What was I thinking. i forgot to add a Foreign key. Which then would make it a 1:M relationship. If I were to make the EmpId in the Checkbox table a FK that should relate the two tables.

  6. #6
    Join Date
    Sep 2004
    Posts
    48

    Red face Another question

    Since the checkboxes would be seperate from the main emp table, the question I would like to ask is. If the checkboxes are section off. Like On-site that has a series of check boxes, Off-site --, Remote --..

    Should I create seperate tables for each section? Or should I clump all the checkboxes and clump them into 1 table?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Ronbegone
    Since the checkboxes would be seperate from the main emp table, the question I would like to ask is. If the checkboxes are section off. Like On-site that has a series of check boxes, Off-site --, Remote --..

    Should I create seperate tables for each section? Or should I clump all the checkboxes and clump them into 1 table?
    I don't understand what you're trying to accomplish and what your requirements are ... It could go either way. Something to remember: There is very few unversal "Best Ways". The best way is the way that your requirements dictate.
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Ronbegone
    If I understand this correctly, you have employee data where some employees (on-site) have data recorded whereas others (off-site) haven't or have different data recorded.

    IMO you can store all this in one table. You will not be breaking the normalisation rules. However, you will have gaps in your tables where certain fields are not relevant to certain employees. Also, you will be storing redundant data. If you use checkboxes, every employee will have to store a 0 or -1 regardless of whether that checkbox is relevant to them. Space and speed may not be an issue if your database is small. Consider how you will want to aggregate info e.g. how many people have checkbox3 as No (where checkbox3 is relevant to offsite folk only) ? You would have to ensure your query selects off-site people only as all other people would default to no. As M Owen say, there is more than one way to design and much of it comes down to understanding your full requirements.

    Consider a university storing info on Lecturers and Students. We could put this all in one table since they all have a first and last name, address, tel no. etc. But we also want to store info specifically relevant to Lecturers and Students e.g. for lecturers: department, salary. This could still be achived in one table albeit with gaps in the data. But it could be achieved in two tables Student and Lecturer without gaps. It could also be achieved in three tables: Student, Lecturer and PersonalDetails where both Student and Lecturer are linked to PersonalDetails. These are one-to-one relationships. There are merits to each option.

    Back to your question. I'd go for multiple tables but this is subjective as we don't know your full requirements:

    tblEmployee:
    empID (pk)
    LName
    FName
    Address,
    LastInput
    etc.

    tblOffsite
    empID (pk,fk)
    chk1
    chk2
    chk3
    etc

    tblOnSite
    empID (pk,fk)
    chk10
    chk11
    chk12
    etc

    So if you have 10 employees of which 6 are off-site and 4 on-site then you will have 10 employee records, 6 off-site records (relected to 6 employee records) and 4 on-site records (related to 4 employee records).

    HTH
    Chris

  9. #9
    Join Date
    Sep 2004
    Posts
    48
    Chris, that is exactly what I am talking about. After searching other questions similiar to mine, they state that if you use data as column headings that is not robust since if a user needs to add another checkbox they would have to enter a column heading, rather than inserting a row. For example,
    tblOnSite
    ConsultID(Pk)
    empID (fk)
    checkbox (name of checkbox)
    value ( yes/no)

    Is something like this possible? I tried doing this but how do you make a specific row to a certain checkbox?

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Ron

    IMO table structures should be static. So if your checkboxes are variable then they should in the structure you suggest or even in their own table and linked to tblOnSite.

    The example (university) I gave is fixed where there is a specific set of tick boxes which apply to lecturer and a seperate set which apply to students. This has to be built as column heading and the only question is whether you decide to use one, two or three tables i.e. the relationship structure.

    Your latest post implies your check boxes are variable. Perhaps you could explain what the check boxes are being used for. Your table name tblOnSite suggests this is a record of people on site. So column headings should be facts (normalised) about those people.

    An example might be trainingAchieved. So there are a set of tick boxes for a person to be trained on. This might be finite but equally might change in the future.

    We could set up ten checkboxes as headings so each record would represent a person and what they've have/haven't been trained on. The porblem is new training needs could arrive that require you to change the table structure.

    I'd be inclined to go for a list. In fact I'd have three tables here:

    tblPerson
    personID (PK)
    Name

    tblTraining
    PersonID (PK)
    CourseCode (PK)
    DateTrained

    tblCourse
    CourseCode(PK)
    CourseName

    There's no need to have check boxes as such because if a record exists in the training table then a person has been trained and if it doesn't, they haven't.

    The Course table allows extra courses to be easily added and also provides the mechnism for showing the courses available i.e. what should be trained.

    As I say, if you can explain what you are using check boxes for it might be easier to suggest how best to structure.

    Regards
    Chris

Posting Permissions

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