SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you Populate a FOREIGN KEY? Pls help settle a disagreement!

    Using these tables, I have a dumb question?


    employees
    ---------
    empid int, auto, not null PRIMARY KEY
    empnum int,
    fname varchar
    lname, varchar
    deptid, int, Foreign Key.


    departments
    ----------
    deptid. int, auto, not null PRIMARY KEY
    deptname varchar.
    deptlocation, varchar.


    As you will notice, the "deptId" is a FOREIGN KEY in the employees table.
    So many question is, how are foreign keys populated? That is, how do you get data into them
    in the tables they are foreign to? I argue that foreign keys should be set to AUTO increment
    in the tables they are foreign to, BUT my friend disagrees and says they should be manually populated.
    In his view, my input form for employees should include a field for deptid to be entered manualy. Assuming you have 30,000 employees, how can you keep track of the last number, sound crazy to me. He continues, by asking me, what do you do with an employee that belongs to no dept? My answer is to create a temp table to hold them till they are assigned a dept.
    So how do you populate a foreign key field in a table they are foreign to? AUTO or MANUAL?
    Note that most DBs will not support two AUTO Fields.
    Thanks
    assigned.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If deptid in the employees table should have a meaning, then it must refer to an existing deptid in departments
    I.e. you should enter the deptid for a new employee "manually" (but I guess you will use a drop down list filled with valid deptids from departments, not a textbox where the user can enter whatever they want?)

    If an employee belongs to no dept, then you could set deptid (the foreign key) to null - or you could create a "department" called "No department" The latter method is also useful when employees leave the company, e.g. moves to department "Retired", or maybe someone moves to department "Leave of absence" etc etc

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dropdown list is the way it is usually implemented

    from the point of view of the sql, though, it is "manually" entered

    about the "no department" department -- that is ugly, and no improvement on null

    "retired" and "leave of absence" are not departments, they are statuses, and should be recorded in a separate column, and the department column should be null for both of them

    why is everyone so afraid of null? null means "no value" or "unknown" or whatever semantics you want to attach to it

    of course, if you need to distinguish different types of missing values -- such as "not applicable" versus "don't know yet" -- then this is a completely different thread

    but don't compromise the entity integrity of a column by using artificial not-really-real values because you want to avoid nulls

    note, this was not aimed at you, jofa, just me ranting in general


  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937
    why is everyone so afraid of null? null means "no value" or "unknown" or whatever semantics you want to attach to it
    I will use nulls in an alpha field. That's fine by me. However I don't use nulls in numeric fields. Number one reason is because then I need to remember to handle the null value when I've retrieved it into asp or vb, and it never fails that I forget and get burned by it at least once. Number two reason is I have found that resetting things to null is not always dependable and I get some funky errors when doing so. I could probably figure out why it's doing it, but that's one less thing I need to worry about if I don't use the nulls. Plus, I prefer not having to worry about nulls when I'm doing data calculations and comparisons. I would prefer having valid values (in business terms instead of real terms).

    Plus I prefer to know that a value has been chosen for a field, and it just isn't overlooked because people are extremely lazy. Personal preference, I guess.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ...
    about the "no department" department -- that is ugly, and no improvement on null
    ...
    It is?
    I think it's a good idea to move the text "No department" from the code (where you populate the dropdown list and add one extra option for... exactly; "No department"), especially if you want the texts in the dropdown to be written in different languages (and the translations come from the db)

    And the "No department" department should exist in the departments table, of course, ergo no "...compromise the entity integrity..."

    OK, I agree - "retired" and "leave of absence" are not departments, but if you don't have any employee_status table, it's a quick solution to the problem "where to move people when they leave the company"

    And, yes, quick solutions are ugly (but quick)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Plus I prefer to know that a value has been chosen for a field, and it just isn't overlooked because people are extremely lazy.
    no offence, but it sounds like the application designer and/or developer was lazy, not the user

    you want to force a value, force it, don't default it to 0

    as for having nulls interfere with calculations, hey, sql is really good at avoiding that -- aggregates ignore null, and a null in any expression makes the entire expression evaluate as null, so if, for example, you did WHERE deptno = 27, then obviously the rows with null deptno would not qualify

    the real problem with 0 is that it will screw up your calculations

    for example, consider a student grade -- if you don't use null (meaning "hasn't written the test yet" ) and instead default it to 0, then what happens to the student's average grade across several courses? crapola

    the instances where you disallow null and force some default value are way more likely to cause you pain than those instances where you allow it and handle it properly, and if you forget to handle it, then you've sacrificed good design for sloppy programming

    all of the above is IMHO, of course, no offence intended to anyone here [img]images/smilies/wink.gif[/img]

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it sounds like I am back to where I started, no definite opinion on this issue. All you great guys & gals?? have given excellent responses, but the issue is, what is the MOST acceptable way of doing it?
    My opinion is that not assigning an employee a department is a business logic flaw and not mine to worry about. I don't know why, but I was taught to avoid using nulls and I have never bothered to find out why other than you can not use them in calculations etc.
    So is this other guy right or am I right? I am deeply convinced he's got it all wrong. We can not get going on the project 'cos we can't even agree on a db design, specifically, FOREIGN KEYS.
    What will be the impact of leaving FK out?
    We have a total of 22 tables. Imagine the joins!!
    Thanks
    assigned

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I missed the foreign key part, but I've always been taught (and have found it to be basically true) that having nulls in a foreign key is a bad idea. It also prevents you from using referential integrity to ensure valid values.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, assigned, we did get sidetracked

    your friend is right, foreign keys are populated manually

    note: this is "manually" from the point of view of the sql, i.e. not using an autonumber

    consider these departments:

    1 sporting goods
    2 housewares
    3 appliances

    now suppose you used an autonumber for employees.deptid

    your first three employees would be assigned one each to those departments, and your 4th employee would get a deptid for a department that doesn't exist

    instead, when a new employee is added, that new record gets a deptid that is "manually" chosen from among the valid deptids from the department table

    again, "manually" from the point of view of the sql -- somebody in HR wouldn't have to enter a deptid number, they would simply choose "housewares" from the dropdown list, and the program logic would assign the right foreign key value

    the only place an autonmuber makes sense is for departments.deptid, the primary key

    as far as "not getting going on the project" is concerned, you MUST take the time to get the db design right

    an extra hour to get the right design will save you an extra month of coding

    trust me on that one

  10. #10
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do not quite understand your intention. Are you talking about making the deptid column in employees an auto-increment column? If so, that is not the way to do it. Basically, you do not auto-increment the deptid field in the employees table, as any value you enter in it must already exist as the value of the deptid column of one of the records in the departments table.

    That was quite a mouthful...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dave, nulls do not prevent you from using referential integrity

    a null foreign key is perfectly valid, it's used for optional relationships, and simply means that this row does not participate in this relationship

    example:

    create table personnel
    ( pers_id integer not null primary key
    , pers_name varchar(100)
    , parole_officer integer null
    , foreign key (parole_officer) references personnel
    )

    obviously you want the relationship optional for people who don't have a parole officer, and null does that

    a "default" value, to avoid the dreaded null, such as 0, which is rationalized away by saying "but there will never be a 0 primary key" merely subverts the functionality that the database should be doing for you, and makes your code more bloated

    simple example, maybe a bit dumb, but you get the point, i hope

  12. #12
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I will accept reasoning presented by r937, even though I don't quite understand or believe it. Then here is where I am now confused by accepting the reasoning. I have always done it the other way round and it has always worked and I don't see why I should change. I guess, we have too many egos in the project, two dbas, two programmers, marketing guys who think they are programmers, HR, dept that thinks they know what they want and the list goes on!!!.
    I am going out for a beer!!
    Thanks to all.
    assigned

  13. #13
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jofa
    If deptid in the employees table should have a meaning, then it must refer to an existing deptid in departments
    I.e. you should enter the deptid for a new employee "manually" ...
    Quote Originally Posted by duckie
    ... Basically, you do not auto-increment the deptid field in the employees table, as any value you enter in it must already exist as the value of the deptid column of one of the records in the departments table.
    etc etc

    Seems to me everyone agree with Assigned's friend

    Just to give another example (don't know why we need it, after rudy's extensive departments example... ):

    Parents and children; it's not the child that give the parent a name, the child just references the parent when (s)he answers the question "who's your daddy", for example (parent name = foreign key)
    The person that can change / assign a new parent name (auto-increment or whatever you prefer), is the parent him/herself
    If the parent name changes, then the foreign key should be updated too, but you never update in the other direction

  14. #14
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So does it mean I have been wrong all along and know it?
    So why has it worked for so long? I read Rudy's and now yours and after two beers, I guess I can see it now. Still don't know why things I have done the other way round always worked.
    Thanks to all. It's been a learning experience.
    assigned

  15. #15
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Assigned,

    My guess as to why it has always worked for you is that an AUTO_INCREMENT column will only automatically assign a value when you try to insert NULL into it.

    If your SQL INSERT queries were always assigning a valid department ID, then the AUTO_INCREMENT feature of the column would (thankfully) never have come into play.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Babys got backa hard back, that is: The Ultimate CSS Reference

  16. #16
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just wondering; can you have two auto_increment columns in a table?
    If that's allowed, what would be the point in having two or more auto numbered fields?

    (My guess: no point)

  17. #17
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well guys, you guys are right. I spent the rest yesterday looking over things I have done in the past and as Kevin and the rest of you guys summized, it has always worked for me in the past 'cos, it seems I have intententionally or unintentionally, avoided stuff I did not understand and written codes around them and this is one of them. Holly cow!! Why nobody has caught this for so long beats me.
    I am now cught with what to do next, continue doing it my way, which obviously is not the right way, BUT works or change to this new way of doing it. Will have another beer over this!
    Thanks guys and gals
    assigned

  18. #18
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It did work in the past, doing it the wrong way, and the reason was that the auto_increment functionality was never used? Isn't that a good reason to, at least, remove the auto_increment from the foreign key?

    Next step; do it the right way, i.e. let the db handle reference integrity stuff - if that's possible in mysql...

  19. #19
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Talking about people doing db things the wrong way: I've seen projects where someone adds lookup tables to the db (e.g. a departments table), at the same time someone else write hard coded values for drop down lists (e.g. options for department names)

    You don't have to think much to realize what will happen when the values in the lookup table are changed, and users complain that the options in the <select> list are the same as before...

  20. #20
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    MySQL does not allow more than one AUTO_INCREMENT column per table. Other databases (e.g. PostgreSQL) do.

    MySQL does not support enforcing referential integrity. If you need a free database that does, PostgreSQL is a good one to look at (though it comes with the headache of a smaller user community, less development activity, and relatively poor documentation).
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Babys got backa hard back, that is: The Ultimate CSS Reference

  21. #21
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could also say that MS Access is a free alternative, supporting referential integrity, because you don't need Access to create an mdb file.
    But then you will have the headache of file permissions and poor support for more than one user (= bad for web solutions)

  22. #22
    SitePoint Member Shroom's Avatar
    Join Date
    May 2003
    Location
    tucson AZ
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    General Thread comments

    Quote Originally Posted by jofa
    You could also say that MS Access is a free alternative, supporting referential integrity, because you don't need Access to create an mdb file
    Keep in mind also MSDE (MS data engine) is MS SQL server without the server tools. While this is a large pain for DB creation and maintenance, the DBs can be created on a SQL install and then loaded with the MSDE engine. MSDE is artificially limited to seriously degrade performance after 5 users.

    General comments on the thread, foreign keys are always manually entered, never auto-created, otherwise they aren't foreign keys, just referential linked fields. Semantic difference yes, but important.

    One possible reason for two auto-generated fields, one is a record number and the other a human-used number. For example, a product return system has a record ID as a key field, then also has a RMA number which is given out to customers. While this could be the same number, sometimes for non-system (ie human) reasons it's a different number series.

    Something else to keep in mind, when you are generating 2nd normal relationships (key-and-lookup tables) is how often the data will be retrieved, and which bit of that data will be the key. If, for example, your lookup is for report purposes and you need to view the human-readable text, the extra step of a join via a foreign key makes sense, as such data is largely duplicative, and once cached, the joined table does not add noticeable overhead. Generally, such lookups do not need direct indexing because the lookup key in such cases is generally a date or employee number. However, a two-column key/data table should be cluster-indexed because (at least for MS SQL) the cache engine is one step less than the data retrieve engine.

    If on the other hand you are displaying discrete single data items infrequently (such as a shipping carrier on a bill of lading) for reprint purposes (you get a few hits an hour at most), and the primary purpose of the data is historical, then 2nd normal form is not needed at all, and the joined lookup will actually take more time than storing the text string in the table. (this assumes a short string and reasonable number of records, less than 100,000 in the table)

  23. #23
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shroom
    Keep in mind also MSDE (MS data engine) is MS SQL server without the server tools. While this is a large pain for DB creation and maintenance, the DBs can be created on a SQL install and then loaded with the MSDE engine. ...
    Can you say MSDE is a free db? I'm not sure...
    Q. Can I use MSDE as database for Web applications?

    A. MSDE is an ideal solution for low-end Web applications. The following products and services enable you to redistribute MSDE as a database server for Web applications:

    * ASP.NET Web Matrix*
    * MSDN Universal, Enterprise, and Professional subscriptions
    * Office XP Developer Edition
    * SQL Server 2000 Developer, Standard, and Enterprise Editions
    * Visual Studio .NET Architect, Developer, and Professional Editions
    * Visual FoxPro 7.0 or 8.0

    http://www.microsoft.com/sql/howtobuy/msdeuse.asp

    Thanks for the comments on keys and relationships

  24. #24
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use MSDE for all my ASP MS SQL designs and have had no problems except ofcourse designe flaws. It's a great tool if you have access to SQL full blown. I run MSDE when at home for designs and then move the Db over to full blown version when I get to work.
    There are some open-source tools that gives you something similar to the enterprise manager.
    assigned

  25. #25
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Assigned
    I use MSDE for all my ASP MS SQL designs and have had no problems except ofcourse designe flaws. It's a great tool if you have access to SQL full blown. I run MSDE when at home for designs and then move the Db over to full blown version when I get to work. There are some open-source tools that gives you something similar to the enterprise manager.
    I (and no doubt others before me) have posted this before, but just so that everyone knows, you can simply download the SQL Server 2000 trial, and install the "Client Tools Only" option. Fully backwards compatible with SQL Server 7 and both versions of MSDE. No expiry, job done!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

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