SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 36 of 36
  1. #26
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know - you're quite right, and no offence taken.

    I get you with the composite PK - that idea in itself is news to me. So presumably if any record has all four of those the same, it'll flag as a duplicate.

    With the Profiles, I'm not so sure about, and would still think they would go in a separate table. In this case they refer to the Employers, but no Profile, or combination of Profiles is unique to each. Also, the same Profiles also link to a Candidates table and a Vacancies table, that is all used to link Candidates to Vacancies by the Profiles that they match on.

  2. #27
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by duklaprague View Post
    I know - you're quite right, and no offence taken.
    thanks

    I get you with the composite PK - that idea in itself is news to me. So presumably if any record has all four of those the same, it'll flag as a duplicate.
    Yup.

    With the Profiles, I'm not so sure about, and would still think they would go in a separate table. In this case they refer to the Employers, but no Profile, or combination of Profiles is unique to each. Also, the same Profiles also link to a Candidates table and a Vacancies table, that is all used to link Candidates to Vacancies by the Profiles that they match on.
    That's your call. I only know of the tables you have shown us and my suggestion was using those as the basis. The key point is;

    1. if your profiles are specific to any one record then it should be added to that record.
    2. if the profiles are to be referenced by more than one record, then they need to go into a separate table.

    However, in deciding on 1 & 2 above, it is important to know if the profile info will always relate to >1 record in the same way. If, for example, profile #1 relates to employers 1 & 4 today but next month the profiles of the companies change, what happens to the profile then? does it become changed to suit one employer and, if so, what do you do for the other employer.

    It's your db but, I would be inclined to look more closely than I can, and consider keeping it one profile per employer. thats a notion beased on limited knowledge of your db.

    Otherwise (and I just thought this after posting) maybe the profile doesn't relate to the employer specifically? Maybe it is a stand alone record, perhaps, which you can relate employers and potential clients to by referencing the profile from each of their respective tables? (food for thought).


    bazz

  3. #28
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As another thought:

    What about your naming convention? employers seems a bit off the mark: maybe it should be 'companies' and contacts should perhaps be 'employees' ro maybe it should be 'contractors' and 'sub-contractors'. its a bit confusing when it gets to 'employersContact'. For that I would be expecting the phone numbers and addresses of the employer. You should consider this for the future when a new person might have to work with the DB.

    bazz

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    What about your naming convention? employers seems a bit off the mark: maybe it should be 'companies' and contacts should perhaps be 'employees' ...
    search party data model

    if you want to be rigourously comprehensive: http://www.tdan.com/view-articles/5014

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    As another thought:

    What about your naming convention? employers seems a bit off the mark: maybe it should be 'companies' and contacts should perhaps be 'employees' ro maybe it should be 'contractors' and 'sub-contractors'. its a bit confusing when it gets to 'employersContact'. For that I would be expecting the phone numbers and addresses of the employer. You should consider this for the future when a new person might have to work with the DB.

    bazz
    I'm not so sure about EmployerContacts, I agree, but the reaon behind Employers is to do with it all being around recruitment.

    Originally, it was Candidates and Vacancies. So Employers are the companies who would potentially employ Candidates, hence Employers.

    So the Candidates would be the (potential) Employees, rather than the EmployeeContacts, who would be more like human resource types, rather than the engineers etc who are in the db as Candidates.

    Anyway - Sunday, so I'm off to read up on those links you posted earlier....

  6. #31
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did I imagine it, or were a couple of recommended sites mentioned earlier in the thread for reading up on indexes and constraints?

  7. #32
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I havenlt looked back but seem to recall referring to them without actually linking to anywhere. I think you have been using phpMyAdmin so, look at the mysql docs from the link at the top of the nav menu and search for constraints and indexes. There is alos a link from r937's sig which is a huge wonderful resource.

    bazz

  8. #33
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here;s a link to one of rudy's articles. 'referential integrity' for which constraints perform a central role.

    http://r937.com/relational-integrity.html


    bazz

  9. #34
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess maybe I did imagine it.

    I had a look at guelphdad's link, and think I'm kinda doing it right - although from what I remember on normalisation there are (at least) three levels of normalisation.

    But in the structure I have, I've split Contacts out from Employers, so that each Employer only has one record, rather than, say, three records each with the details of different contacts.

    And linked using EmployerID (PK) in the Employers table, and EmployerID (FK) in the Contacts table.

    Similarly with Profiles, I have

    ProfileID, Profile
    1; 737
    2; 757
    3; B10
    etc

    So then all the Profiles can be linked back to other tables via a look up table.

    Rather than having, say

    EmployerID, Employer, Profile
    1; British Airways; 737, 757, B10
    2; Qantas; 737, B10
    3; Virgin; 757, B10

  10. #35
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup but rememebr that a PK does not have to be numeric. your profiles table could be

    Code mysql:
    create table profiles
    ( profile varchar (24) not null
    , Primary Key (profiles)
    CONSTRAINT .......see earlier post
    ) engine .... see earlier post

    bazz

  11. #36
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - I've been looking at this, and think I'm going to have to look at the ins and outs of all of this stuff when I start my next database project.

    This one is so far along, that I'm going to (for the time being) stick with what I've got. It may not be normalised to the nth degree, but it seems normalised to some degree, in that I've split out data into separate tables, rather than duplicate date unnecessarily in the same table - which is what I thought was the main thing. And the database isn't (and isn't likely to be) very big - there will be maybe 50 employers, each with maybe 5 contacts.

    I will come back to this for future projects though, I promise, and appreciate the pointers so far.

    I've started a new thread on a repeat region I'm having trouble with, which should (for the time being) be the last little thing to do.


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
  •