Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: I think it's finished...

    I think I finished the layout of the tables in this new DB. It looks really weird to me though cause I used a bunch of look-up tables so they all point all over the place.

    Anyone want to take a look and let me know if they see anything wrong or any issues?
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Looks like spiderman upchucked a spider web onto the relationships form (just kidding - I always say this when I see a large number of tables joined together - nothing wrong with that - just means that you've got your hands full with a big project (I love working with these kind of projects).) Without going through every relationship, the thing which stood out is that you may want to click the "Join Type" button on the links between the tables and possibly make sure you don't need to utilize 2 or 3 (ie. include all records from table XXX and only those records from table YYY where the joined fields are equal.) Also, do you want to enforce referential integrity? I might set this option and also the cascade update/delete options (to prevent orphaned records which is nasty.)

    You kept the field names the same in both tables which I always suggest doing - nicely done with setting up consistent names for joining fields and adding ID at the end of the name to distinguish them as identity type fields. I like to see field names such as OfficePhoneID defining an autonumber field. Linking on fields which are named differently or don't have ID in the name has a tendency to produce confusion later on. Kudo's for even supplying descriptions for all the fields in every table and again, nice job on using a good name scheme for field names (ie. no spaces in any names and a name which makes sense for what kind of data will be stored.) You even started each table name with the wording tbl which I really like.

    Only other suggestion I would have would be is on the joining field (defined as a number in the secondary table) - remove the default value of 0. I personally don't like to have any default value for a joining field.

    Without understanding the full context of the design, one other thing I might do a little differently is on the tblWorkPhone. Instead, I might call it tblPhoneNumbers and add in another field into the table and call it PhoneType (ie. Work, Home, Fax, etc...), and change the name Work_Phone to Phone_Number. And of course make this table a one-to-many relationship. This would give you a little more flexibility on storing multiple types of phone numbers in that table but it's only a suggestion. You might be able to get rid of tblOfficialPhoneNumber and tblOfficePhone by doing it this way. If you want to keep them separate, then I'd instead read the next suggestion below and actually store in OfficalPhoneNumber, OfficePhone and WorkPhone in the main tables (ie. tblSupervisor and tblInspector) itself versus a relational table. It will help save you some dancing around with queries. Having tblWorkPhone joined to both tblInspector and tblSupervisor could be problematic if you need to return both the Inspector and Supervisor's phone numbers in 1 query. Same with the other phone number tables and the way they are set up to their main tables. I don't really like the way you have the phone number fields joined to multiple tables.

    One last thing which I've done is, I would also store in fields such as Gender in the actual tblIndividuals itself, using tblGender as a lookup table only (ie. for the combobox on the form.) The same with some of the other tables where you only have 1 field in the relational table. The reasoning on this is that you are storing an ID number in tblIndividuals and sometimes it's easier to just have the Gender itself stored so you don't need to link in an extra table in any queries to show what the Gender is (you could also keep in the GenderID field in tblIndividuals but this is a little redundant.) It's something to consider to prevent having to link several tables in future queries just to get information for 1 field (if you eliminate tblGender, you can code in the values allowed into the combobox itself on the form.) This way you're dealing with 1 field (ie. Gender in tblIndividuals) versus 2 ID and 1 Gender fields and 2 tables plus the relationship (I try to minimize the amount of tables I'm dealing with.) I'm not sure how many records you're planning but every linked table in a query slows it down that much more and I try to minimize having to link in tables such as tblGender just to see what that Gender is for an individual. It can be a pain. There are some other tables like this where you can utilize the relational table as a lookup table (with no join at all or even join on the actual Gender name field between the tables versus the GenderID field - nothing says the joins always have to be on an ID type field.) Linking on Gender versus GenderID would mean that users cannot add a new type of Gender in tblIndividuals unless it is already in the tblGender table (depending on how you create the join.) Doing this with some of your other tables might save you some spider-web looking queries where you have many tables linked just to get information for 1 or 2 fields from another table. - Whenever I see a relational table with just 1 field (besides the ID field), I always consider whether it would not just be better to put that field in the main table versus a relational table to minimize having to always join that table in future queries to get the value.
    Last edited by pkstormy; 07-17-08 at 23:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    I see how it is! Bash on the guy! That's messed up! I'm just kidding around.

    I didn't do the joins, they're only because of look-up tables, not manually put in. I didn't even look at them yet. I will take your advice and look into it and see what I can do.

    Thanks Paul

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I added some comments. Not sure if you saw the additional comments I added as you responded while I was working on my post. No bashing involved - I wanted to give you my honest feedback (and hopefully make some thing easier for you.)
    Last edited by pkstormy; 07-17-08 at 23:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    I added some comments. Not sure if you saw the additional comments I added as you responded while I was working on my post. No bashing involved - I wanted to give you my honest feedback (and hopefully make some thing easier for you.)
    I was just playing around Paul. I appreciated all your help with the last DB that never got used after 6 months of our work together. And any help I get with this one would be awesome also! Thanks in advance!

    **EDIT**

    Paul,

    I read your comments and will take them into suggestion. With the Gender table, the original way that I had it organized was just a single field in the tblIndividual, as you say to do also. Then when someone else on the forums looked at it, they suggested to separate them. That's probably why there's so many tables.

    I put in the comments in the tables or description, so the "Official Phone Number" is a telephone number that goes to an individuals' blackberry device. This is also why I had the tblWorkPhone this way with the people as I figured that since people move

    around, their phone numbers would change so I didn't want it just relating to a single individual, same with the "Official Phone".

    **Edit 2**

    Paul,

    If I do it the way you're suggesting...Rather than making the field a 'lookup off an ID and the actual field', I could change the following, correct??:

    1. Gender (Should only be able to select from Male / Female)
    2. Ethnicity (Should only be able to select from known ethnic backgrounds [ie: Caucasian, White - Non Hispanic, Hispanic, etc)
    3. State (Should only be able to select from US states, Canadian territories, and border states of Mexico-United States).
    4. Continent (should only be able to select from the continents that I put into the DB, not add a new one)
    5. Country (should only be able to select from the countries that I entered already, not add a new one)
    6. ACS / ECS / WatchList PosNeg (should only be able to select Positive or Negative).


    This would ONLY allow the user to select from the entries in those fields that I selected, yes? That could also mean that I can change the field names in the tables to like "Gender", "Ethnicity", and "State" versus having the "ID" after it, correct? I don't want the users to have free reign all over the DB and some things, like the above mentioned, should only be able to add to / take away by me, the DBA.
    Last edited by Grafixx01; 07-18-08 at 11:46.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I'm also not too sure that the "Cascade Delete" will be a good function for this db. The "Cascade Update" would be though. Now I just gotta figure out where, how, and why so I know what's going on with the DB if asked questions.

  7. #7
    Join Date
    Aug 2006
    Posts
    559
    Does this look better? I set up the joins / referential integrity for them, though I'm not sure they're set up correctly.
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Grafixx01,

    I'll need to look at your design some more to give you a better diagnosis. At first glance, there seems to be a lot of relationships going in different directions and this seems a little wierd.

    Regarding the lookup type tables - Yes. Instead of having an ID type field in the main table, have a value field such as Gender. Then create your lookup type table (ie. GenderID and Gender). Then just link Gender to Gender and set the join type. This will then not allow users to enter a new Gender value in the main table which is not in the lookup table and also makes it so you don't always have to link the relational table to get the Gender. I'm not sure why some people think it's better to always have to link a bunch of relational tables in a query to return 1 simple field when it's so much more efficient to not have to link those tables in - this just doesn't make sense. I think it stems from "over" normalization thoughts but having a field such value such as "Male/Female" duplicated in the main table far outweighs having to deal with a relational table in all your queries. It's kind of like creating several relational tables on an Address type table, one for City and one for State and one for Zip. This doesn't make sense just to prevent duplication and it's much easier returning a full address having City, State, and Zip in the address table. You would have to join 4 tables just to get a complete address which really isn't efficient and what I would call "over-normalization." You can do the same for 2-6 above.

    Another way if you want to avoid even having a lookup table for Gender (and the other lookup tables), is to actually put the values in the combobox on the form itself. You would then set the combobox to limit to list. For things like Gender, you probably won't have other values than Male and Female (although not to forget some of today's new type of Genders.) In the combobox, you set the Query type to "Value List" and then enter the values below that: Male;Female. This is optional on how you want to do it and I've done it both ways, with a lookup table, and in the combobox itself.

    I'll try to look at your relational structure when I get more time. Again, at first glance, it seems a little wierd the way you have it.
    Last edited by pkstormy; 07-19-08 at 09:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pkstormy
    ... and also makes it so you don't always have to link the relational table to get the Gender. I'm not sure why some people think it's better to always have to link a bunch of relational tables in a query to return 1 simple field when it's so much more efficient to not have to link those tables in - this just doesn't make sense. I think it stems from "over" normalization thoughts but having a field such value such as "Male/Female" duplicated in the main table far outweighs having to deal with a relational table in all your queries.
    excellent analysis

    having a relationship to a "lookup" table, for the express purpose of ensuring data integrity (cannot use a value that isn't defined in the lookup table), is a design feature that seems to escape so many otherwise competent people

    i blame the ubiqitous and evil surrogate key for this sad situation

    when the foreign key is GenderID instead of Gender, little has been gained except a few bytes, and a lot has been lost, namely, the performance benefit of never having to do the join to get the value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks Rudy.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sample Lookup Relational Table

    Regarding lookup type tables, I've always been in favor that you can satisfy both limiting the "required" data entry values and also meet the efficiency of not having to always link in the Lookup table in queries.

    I've attached an example showing how this can be done in utilizing a lookup table and if the lookup table is removed, the example form shows how to meet the data requirements using a combobox on the form.

    You will notice in the example, with the relationship created, you cannot enter a value into the Gender field in tblCustomers which is not currently in the tblLookupGender. Also notice that I did not set the join properties to Cascade Delete (this would not be wise to do as it would cause deletions of the entire record(s) in tblCustomers should that value be deleted in the lookup table!!) It would be important that you experiment with the Cascade Delete option in the join properties so you know what happens to the records in tblCustomers should you change the Cascade Delete option.

    Setting the Cascade Update option would be a decision you'd want to make. I normally set this to true with the intention that should I change a value in the lookup table, I want that value to change for all records in tblCustomers. Unchecking this option means that you would need to add the new value in tblLookupGender, then change the values in tblCustomers to the new value, then delete the old value in tblLookupGender. Unchecking the Cascade Update option could then be a safety measure as to ensure users don't change a value in the lookup table which could then change a mass amount of values in tblCustomers (and possibly have a negative impact on certain reports.)
    Attached Files Attached Files
    Last edited by pkstormy; 07-19-08 at 15:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Sample Lookup Table (in Access 97)

    Here's a 97 version of the sample lookup table.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Thanks Paul for the help. I'll take a look at what you uploaded and what you've said. I've changed it up a bit to pull just the second column from the lookup tables (ie: Gender from tblGender, rather than Gender ID from tblGender). I figured originally, that I should have done it that way but always second-guess myself.

    **EDIT**

    Here's what might be a dumb question...

    In the table that I have like "tblCountry", I have a PK of another table (tblContinent). I did this because I want to make sure that certain countries belong to certain continents and no others. Is that right? Wrong? What?

    For instance, if I make an entry in the tblCountry (Iraq) and I want it in the tblContinent of Southwest Asia ONLY, I would select the Continent on the form for it. The Continent is really just a lookup table.

    |||| Then if I do it that way, can I automatically populate "Continent" on the Individual's Passport information using just the tblCountry when the user selects it? You know what I mean? Like how on some registration forms on the net, when you select like "United States", it'll pull up the states that are in the US? That's what I want it to do but make the "Continent" field LOCKED so nobody can edit it.
    Last edited by Grafixx01; 07-21-08 at 10:55.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think I follow what you mean regarding tblCountry and tblContinent. Instead of separating these into 2 separate tables though, why not just have Con_Name and Con_Abbrev fields in the tblCountry table? I'm assuming these are lookup type tables. Again, duplicating Con_Name and Con_Abbrev in the tblCountry table would not necessarily be bad and would prevent you from having to link the 2 tables together to retrieve the Continent information. It would again, save you from having to always link the 2 tables together. This is strictly optional though and either way works.

    What I envision you then doing is having a combobox which has all 4 fields as part of the rowsource query (ie. Country_Name, Cntry_Abbrev, Con_Name, Con_Abbrev.) When a Country_Name is selected in the combobox, you could then easily use some code to populate your other fields with corresponding values from columns the combobox rowsource query. Here's an example of what I mean and code for the combobox after a Country has been selected:

    Private Sub CountryCombobox_AfterUpdate()
    if not isnull(me!CountryCombobox) then
    me!MyDataFieldStoringContinentName = me.CountryCombobox.column(3) (<- or whatever column the Continent name is.)
    end if
    End Sub

    What this essentially does is populate your data field on the form storing the Continent Name for that entered record with the column in the query which has the Continent Name (ie. Con_Name). Your me!MyDataFieldStoringContinentName could then be locked on the form to prevent users from editing the value. Updating the combobox with a new Country would then automatically put the Continent Name into the locked data field which stores the Continent Name on the form for that record. - I hope you follow. You could do the same for any of the other columns (ie. Con_Abbrev if you want to transpose the Continent abbreviation to a data field on your form.)

    Attached is a very quick example on what I mean. I hope it shows what you're trying to accomplish.
    Attached Files Attached Files
    Last edited by pkstormy; 07-21-08 at 17:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Aug 2006
    Posts
    559
    I'll take a look at doing it that way. I'm also trying to make it so that users cannot add in their own continent. I've already designated what countries (as of right now) and continents there will be in the DB, so I don't want them to change it.

    I know I don't even have to show this field on the main form, but the users want to be able to query and find out how many people may be exiting / entering from a certain region or continent, that's why the field is in the db.

Posting Permissions

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