SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 36
  1. #1
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database / table structure and the SQL syntax help

    Hope someone can point me in the right direction here.

    I basically have a database structure like this :

    table_Employers : EmployerID, Employer, Address etc

    table_EmployerContacts : ContactID, EmployerID, Name etc

    table_EmployerContactProfiles : ContactID, ProfileID

    table_Profiles : ProfileID, Profile

    From these tables I can -

    link EmployerContacts to Employers, so list each Employer, with its contacts.

    link EmployerContacts to Profiles, so list each EmployerContact and its associated Profiles.

    However, what I've now been asked to do is have an EmployerProfiles table rather than an EmployerContactProfiles table, because the Profile for each Contact within an Employer will be the same.

    The idea being that you add the Profile to the Employer, and it propagates down to each EmployerContact with a matching EmployerID.

    I hope that makes sense, and if anyone could let me know if that would be possible, along with some pointers as to the database / table structure and the syntax of the SQL queries, that would be much appreciated!

    Many thanks.

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I don't fully get what the tables represent

    I would try this as an attempt either to give you a pointer or help you along some other way.

    employer
    employer_id | employer | address

    contacts
    employer_id | last name | first name | profile data


    I have done that layout on the basis that:

    1. each persons profile is unique to them
    2. one employer might have more than one contact

    oops, I didn't read all your post. this homework?

    bazz

  3. #3
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope - its not homework, its a site I've been doing for someone I know. It started out pretty basic, but has kinda grown quite a bit. I come from a design rather than coding background, so the SQL and PHP stuff I can kinda follow in hindsight, but doesn't always come as naturally as it might.

    The Profiles aren't unique to each Employer / Contact.

    The background is that its a database for an aviation recruitment agency - so each Employer deals with particular aircraft or engineering skills.

    As its currently set up, there's a page to add a new Employer.

    And a page to add a new Contact, on which an existing Employer can be selected to attach the new contact to the selected Employer.

    After the page where the name and contact details are added, there's a page with various aircraft / engineering skills listed (the Profiles), and as many can be selected by checking checkboxes. (And they won't necessarily be unique to each contact.)

    So from all of that, from a list of Employers, there's a click through to an Employer Details page, which has a second recordset that lists all the contacts attached to that Employer.

    And from there, a click through to a Contact Details page, that lists the name, contact info etc, as well as the Profiles that were added.

    So what I'm looking to do, is, I guess, link the Employers table to the Profiles, rather than the EmployerContacts table.

    But the trick I'm not sure about is how to link that to each contact.

    So basically, have a page to add the Employer, then go on to add the Profiles for that Employer, just as is currently done for the EmployerContacts, but have those profiles somehow linked to each EmployerContact as well.

    Not sure how well I've explained that, but hopefully makes some sense?

  4. #4
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I may have figured out a way to do it. It can probably be done by more efficient means using complex SQL, but it should be do-able by adding yes/no checkboxes to the page where Employers are added.

    And then, as its a simple enough join between Employers and EmployerContacts, each EmployerContact will inherit the correct Profiles, based on which checkboxes were checked when the Employer was added.

    I think, at least....

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to be slow but is this what you have? (I mean in terms of table content ~ forgetting for now, about what scripts you might use).

    employers

    employers contact details

    employer profile/skillset

    Can you explain the employers contacts table? Are you saying that different employers wil have the smae contact details?

    tip:
    get your database planned, normalised and built before thinking about the scripts/pages.

    bazz

  6. #6
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not quite - its

    Employers - ie a particular company.

    EmployerContacts - the actual contacts within each company.

    Each Employer / Company can have multiple contacts, so as that's a one-to-many relationship, I've split them out into two tables.

    And similarly, each EmployerContact can have many Profiles, another one-to-many relationship.

    So it was all pretty much there - Employers was linked to EmployerContacts, and EmployerContacts was linked to Profiles, via a lookup table, with just ContactID and ProfileID.

    But now I want to try to link the Employers to the Profiles - straighforward enough, but have each Contact inherit those Profiles, which would be the part I'd be less sure of.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by duklaprague View Post
    Not quite - its

    Employers - ie a particular company.

    EmployerContacts - the actual contacts within each company.

    Each Employer / Company can have multiple contacts, so as that's a one-to-many relationship, I've split them out into two tables.

    And similarly, each EmployerContact can have many Profiles, another one-to-many relationship.
    So would it be true that contacts would relate to the employees of the company?

    so you could have

    employers

    employees

    contact

    profiles

    (profiles related to an employee, contact to an employee and employees related to employers?)

    bazz

  8. #8
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess it would be

    employers

    employees

    profiles

    Where the profiles were linked to the employers, but those profiles were inherited by each employee.

    I don't think it needs a separate Contact table.

    The Employer table would hold the Employer name, address, switchboard number etc

    And the Employee table would hold the name, job title, direct dial number, email address etc.

    Which is why I think now it might be as easy to just add in some yes/no fields for the Profiles into the Employer table.

    Thanks for replying so far - I think I'm going to have to call it a night here as its 2am!

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The night is young at 2am

    I normally end up finishing at about 5 otherwise, I lose my train of thought.

    Anyway, I think it might be a good idea to plan out on paper perhaps, some sample data and see how best it should fit in to a structure.

    you could have

    employers
    employer_id(PK) | employer details |

    employee
    employee_id (PK) | employer_id (FK) | personal phone number | profile details |

    contact
    |employee_id (FK)| contact type | phone | fax | address_1 | address_2 |etc
    | 0000000001 | office |
    | 0000000001 | home |

    If as I think, a profile is a bit like a small CV/resume of qualifications, etc then it is unique to the employee. you and I might have mysql as one of our attributes/aptitudes/qualifications but all others of our skillset would be different, making the overall profile unique to each person.

    I don't think you need contact details in the employers table. aftre all, you won;t be phoning the company, you'd be calling an employee of the company.

    just my thoughts. I am only an expert in my sleep.

    bazz

  10. #10
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sure I'd be up longer if I didn't have work in the morning!

    I know what you mean about planning, but the site and its functionality have kind of evolved - with the guy running the company thinking of new stuff that would be handy.

    I'd have thought that the address details should be in the Employer table though, as this will be the same for each Employee at that company.

    Otherwise, aren't you duplicating address info in each Employee record at the same company?

    I originally thought each Employee / Contact would have a unique (or at least their own, if not unique) set of Profiles.

    But in fact each Employee / Contact at the same Employer would have the same set of Profiles.

    Its more about the services the company provide, than what and individual at that company provides.

    So the idea is to be able to attach a list of Profiles to every Employee at the same Employer in one go, rather than attach that same list multiple times to each Employee.

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by duklaprague View Post
    I'm sure I'd be up longer if I didn't have work in the morning!
    Ah I forgot about that possibility. I work to my own clock which is based on GMT with a variable multiplier, which means my timezone (BRT - bazz real time) can be +/- 5 hrs gmt

    I know what you mean about planning, but the site and its functionality have kind of evolved - with the guy running the company thinking of new stuff that would be handy.
    ok. It may be easier to redo the whole db instead of changing what is there. (Your call). If you check out its degree of normalisation you might get a surprise. I have read that, often, when a db is scaled up it sometimes becomes inefficient because data which is already in it somewhere, becomes duplicated for ease of query/insertion, etc.
    Causes more issues in the longer term.

    I'd have thought that the address details should be in the Employer table though, as this will be the same for each Employee at that company.
    If they are the same for each employee then I would agree; they should be in that employer table. But if the employees have personal contact details as well as (default) office details or, they have some values that differ then, I think, the extra/personal details should go into a contact table.

    ... in fact each Employee / Contact at the same Employer would have the same set of Profiles.

    Its more about the services the company provide, than what and individual at that company provides.
    OK, so which is true:

    the company has a single set of profiles across the workforce
    a workforce team/department, in the company has a set of profiles common to that team and they differ from other teams
    each individual worker has his own profile.

    That should help you become certain as to what table the profile data should be in or (if in its own table) how to relate it to the appropriate team/employee/office

    I hoipe it also points you towards building the db for future employers who may need individual employee data to be kept separately. Again, you know more about that likelihood than I.

    So the idea is to be able to attach a list of Profiles to every Employee at the same Employer in one go, rather than attach that same list multiple times to each Employee.
    I got that but just need clarification of the relationship hence the true/false question above.


    hth

    bazz

  12. #12
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I mostly do the website stuff as a hobby - the day job doesn't always allow me to be creative, so I've kind of taught myself web design, and been able to bits at the day job, but not that often.

    I think redoing the database from scratch would be more trouble than its worth now - not least (and I admit I got help with this) because there's actually some pretty clever stuff going on with the original brief.

    Basically there are Vacancies for jobs, and Candidates for those jobs. So both of those tables link to the Profiles table, and Profiles for Vacancies and Candidates can be added. From that, you can search for Vacancies, and then pull up a list of Candidates with a matching Profile set.

    The whole addition of Employer details has come late to the party. Although you should never say never, this really should be the last part that completes the circle - Employers, Vacancies and Candidates.

    I think we might have wires crossed about the Contacts table, as you mentioned Employers, Employees and Contacts tables. But the Employees and Contacts are one and the same.

    So its one table for Employers (as in the generic company) and one table for Employees/Contacts, for the details particular to individuals at that company.

    The which is true question - the company has a single set of profiles across the workforce. I thought I was nearly there setting it up as a set of profiles for each employee, but the guy is now quite sure that its the same set of profiles for each employee/contact.

    The aim is to attach a set of profiles to each employee at a company at once, rather than add that set of profiles to each employee / contact individually. And I guess that any new contact would adopt when added.

    So really to attach a set of profiles to an Employer, and have that set of profiles linked in to each employee / contact at that Employer.

    Thanks again for posting replies - I know its not easy when someone not totally sure how to do something tries to explain what it is they're trying to do. But even talking it through helps!

  13. #13
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been working on redoing what I'd done for the Contacts, but for the Employers.

    I now have a table structure like this :

    Code:
    TABLE : Employers
    E_ID, E_NAME,        E_ADDRESS
    1        Smith & Co     123 High Street...
    2        Jones & Co      321 Some Road...
    3        Davies & Co    213 Somewhere else..
    
    TABLE : Contacts
    C_ID, E_ID,     Contact Name
    1        1           Mr Smith
    2        1           Mrs Smith
    3        2           Mrs Jones
    
    TABLE : EmployerProfiles
    P_ID, E_ID
    1       1
    1       2
    2       1
    2       3
    
    TABLE : Profiles
    P_ID     Profile
    1          737 Classic
    2          737 NG
    3          757
    Then, on my EmployerDetails page, I have a simple query/recordset to display the Employer fields.

    And a second query/recordset to show their profiles :

    Code:
    SELECT *
    FROM Profiles INNER JOIN (Employers INNER JOIN EmployerProfiles ON Employers.EmployerID = EmployersProfiles.EmployerID) ON Profiles.ProfileID = EmployerProfiles.ProfileID
    WHERE Employers.EmployerID = colname
    ORDER BY Profile ASC
    So based on all of that the bit I'm not sure of is the query/recordset to show the profiles on the Contacts details page.

    If that makes sense!

    Iain

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again,

    The which is true question - the company has a single set of profiles across the workforce. I thought I was nearly there setting it up as a set of profiles for each employee, but the guy is now quite sure that its the same set of profiles for each employee/contact.
    Does this not mean that each employer has a standard profile. If so (and it sounds like a 'yes' to that one), the the profile can go in the employer table either as an FK or with the profile actually added to one or more cols in that employer table.

    The fact that more than one employer may have the same profile, I think actually complicates it - if you make two employers use an FK to the same profile. what if one employer changes their profile? what happens to the other one? I think your db needs to take account of this or else risk being obsolete very quickly due to incorrect data.

    Now, in case you haven't already done so, you need to lok up now, CONSTRAINTS and INDEXES. These are very important matters to address before working on an admin front end or your queries.

    Constraints make sure that if a record in one table is dependent on a record in another, that it will know what to do if you try to update/delete/whatever, that record.

    Indexes make the queries whizz through the records making for a very efficient / optimised db.

    if you can show us your 'create table' statements we can advise further on data types etc. It may seem like a lot of needless work but I assure you it isn't.

    bazz

  15. #15
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Evening! Have been working on it again tonight, and with a bit of guidance, I've got it working I think.

    On the EmployerDetails page, the query :

    Code:
    SELECT *
    FROM Profiles INNER JOIN (Employers INNER JOIN EmployerProfiles ON Employers.EmployerID = EmployerProfiles.EmployerID) ON Profiles.ProfileID = EmployerProfiles.ProfileID
    WHERE Employers.EmployerID = EmployerID
    ORDER BY Profile ASC
    pulls through the Profiles for the current Employer

    And on the Contact details page this does :

    Code:
    SELECT Profiles.Profile
    FROM Profiles LEFT JOIN EmployerProfiles ON Profiles.ProfileID = EmployerProfiles.ProfileID LEFT JOIN EmployerContacts ON EmployerProfiles.EmployerID = EmployerContacts.EmployerID
    WHERE EmployerContacts.ContactID = ContactID
    ORDER BY Profile ASC
    I do still need to sort out the search for this though...

    When you talk about indexes, do you mean Primary Keys? Will have a look at constraints though - my early experience of database design is pretty much from MS Access, so perhaps not the best grounding.

    Not sure about create table queries, as I do all my database structure design stuff using phpMyAdmin - but I can post some datatypes of fields etc.

    But not tonight!

    Thanks again.

  16. #16
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, don't yet worry about your queries. we need to get the db sorted properly as I mentioned earlier for optimum performance and easier long-term db management. (There has been a lot written so it might be useful to re-read the whole thread).

    To post a create statement by using phpMyAdmin follow these steps.

    1. view the table structure of the table you want the statement for.
    2. click 'export'
    3. uncheck 'Enclose table and field names with backquotes'
    4. check the box for ADD CREATE PROCEDURE/FUNCTION
    5. click 'GO'.

    The next page will show you the tbale structure and possibly the actual data. Just copy the create table part and include any constraint if they are showing at the bottom.

    post it here.

    Re your queries:
    The queries come last in the development propcess so don't worry about them yet. If you try to build them to soon in the development process, you wil end up with a db that was built to serve results rather than one which was correct. 'cart before horse springs to mind'.

    Anyway, about indexes.

    When you want to get results from the db, it will take much longer if the query has to scan the whole of several tables becasue it will have to go through data that is much more voluminous than if it were an index. think of the index at the front of a book. Reading it tells you where to get the data without you having to flick through every page. Saves a lot of time.

    There are several types of index: Primary, Unique and plain index. better explained in the docs than I ever could.

    I shall wait til you come back, next time

    bazz

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    To post a create statement by using phpMyAdmin follow these steps.

    1. view the table structure of the table you want the statement for.
    2. click 'export'
    3. uncheck 'Enclose table and field names with backquotes'
    4. check the box for ADD CREATE PROCEDURE/FUNCTION
    5. click 'GO'.

    The next page will show you the tbale structure and possibly the actual data. Just copy the create table part and include any constraint if they are showing at the bottom.
    one step method: go to the SQL tab and run this query:
    Code:
    SHOW CREATE TABLE tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    presactly!!

    But I was trying to coax duklaprague inot becoming more familiar with phpMyAdmin.

    @duklaprague, you could also download heidisql. I did that after the recommendation of someone 'not a millions miles from here' and I found it easier to use.

    @r937, I was hoping you would pop in (and highlight any inaccuracies in my posts).

    bazz

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    @r937, I was hoping you would pop in (and highlight any inaccuracies in my posts).
    have looked in occasionally, but there was so much to-ing and fro-ing that i decided to wait until the final schema had been decided

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

  20. #20
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kinda like me popping in on the 4th quarter of Sunday's game.

  21. #21
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - here goes with the four relevant tables here :

    Employers :

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.4
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Jan 27, 2009 at 10:09 PM
    -- Server version: 5.0.67
    -- PHP Version: 4.4.8
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `myDatabase`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `Employers`
    --
    
    CREATE TABLE `Employers` (
      `EmployerID` int(11) NOT NULL auto_increment,
      `Employer` varchar(200) default NULL,
      `Address1` varchar(100) default NULL,
      `Address2` varchar(100) default NULL,
      `Address3` varchar(100) default NULL,
      `PostTown` varchar(100) default NULL,
      `PostCode` varchar(10) default NULL,
      `Tel` varchar(30) default NULL,
      `Fax` varchar(30) default NULL,
      `Email` varchar(100) default NULL,
      `Website` varchar(100) default NULL,
      `Notes` longtext,
      `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `LastUpdatedBy` varchar(100) default NULL,
      PRIMARY KEY  (`EmployerID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=55 ;

    EmployerContacts :

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.4
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Jan 27, 2009 at 10:12 PM
    -- Server version: 5.0.67
    -- PHP Version: 4.4.8
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `myDatabase`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `EmployerContacts`
    --
    
    CREATE TABLE `EmployerContacts` (
      `ContactID` int(11) NOT NULL auto_increment,
      `FirstName` varchar(100) default NULL,
      `LastName` varchar(100) default NULL,
      `JobTitle` varchar(200) default NULL,
      `Department` varchar(200) default NULL,
      `Tel` varchar(30) default NULL,
      `Mobile` varchar(30) default NULL,
      `Fax` varchar(20) default NULL,
      `Email` varchar(100) default NULL,
      `EmailList` varchar(10) default 'N',
      `EmailList2` varchar(10) default 'N',
      `Notes` longtext,
      `EmployerID` int(5) NOT NULL,
      `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `LastUpdatedBy` varchar(100) default NULL,
      PRIMARY KEY  (`ContactID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=176 ;

    Profiles :

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.4
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Jan 27, 2009 at 10:14 PM
    -- Server version: 5.0.67
    -- PHP Version: 4.4.8
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `myDatabase`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `Profiles`
    --
    
    CREATE TABLE `Profiles` (
      `ProfileID` int(11) NOT NULL auto_increment,
      `Profile` varchar(100) NOT NULL,
      `Category` varchar(100) NOT NULL,
      PRIMARY KEY  (`ProfileID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=186 ;

    EmployerProfiles :

    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.4
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Jan 27, 2009 at 10:15 PM
    -- Server version: 5.0.67
    -- PHP Version: 4.4.8
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `myDatabase`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `EmployerProfiles`
    --
    
    CREATE TABLE `EmployerProfiles` (
      `ProfileID` int(11) NOT NULL,
      `EmployerID` int(11) NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    Thanks for taking the time to go through this with me from the ground up - I started out teaching myself using Access, but probably not from the ground up, but as is often the way in work, to get the desired result, but not necessarily with the training required being made available.

    From there learning HTML/CSS and the natural progression to this sort of stuff.

    Hopefully its not too much of a disaster!

  22. #22
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my suggestion for your table structure but I have added only one Foreign key constraint.

    Code mysql:
    CREATE TABLE if not exists Employers 
    ( EmployerID int NOT NULL auto_increment
    , Employer varchar(200) default NULL
    , Address1 varchar(100) default NULL
    , Address2 varchar(100) default NULL
    , Address3 varchar(100) default NULL
    , PostTown varchar(100) default NULL
    , PostCode varchar(10) default NULL
    , Tel varchar(30) default NULL
    , Fax varchar(30) default NULL
    , Email varchar(100) default NULL
    , Website varchar(100) default NULL
    , Notes longtext
    , LastUpdated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    , LastUpdatedBy varchar(100) default NULL
    , PRIMARY KEY  (EmployerID)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
     
     
    CREATE TABLE if not exists EmployerContacts 
    ( Employer_id int NOT NULL
    , FirstName varchar(100) default NULL
    , LastName varchar(100) default NULL
    , JobTitle varchar(200) default NULL
    , Department varchar(200) default NULL
    , Tel varchar(30) default NULL
    , Mobile varchar(30) default NULL
    , Fax varchar(20) default NULL
    , Email varchar(100) default NULL
    , EmailList varchar(10) default 'N'
    , EmailList2 varchar(10) default 'N'
    , Notes longtext
    , LastUpdated timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    , LastUpdatedBy varchar(100) default NULL
    , PRIMARY KEY  (FirstName,LastName,Mobile,Email)
    , CONSTRAINT contacts_employer_fk 
        FOREIGN KEY (Employer_id) 
          REFERENCES Employers (Employer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
     
     
    CREATE TABLE if not exists Profiles 
    ( ProfileID int NOT NULL auto_increment
    , Profile varchar(100) NOT NULL
    , Category varchar(100) NOT NULL
    , PRIMARY KEY  (ProfileID)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

    You need to add one constraint for each foreign key. on update cascade is just one option. look up 'constraints' in the docs or better - look into the link from r937 above. there is an absolute wealth of knowledge there and one of the links deals with FK constraints (If I recall correctly). I am almost certain I read up on them there.

    You might also need to change some of your values for varchar. does a website really need 100 characters of space? I seem to recall that they are all max 64 chars (not including query strings)

    I have changed your PK in the EmployerContacts table. it needs to have the column names that would be sifficient to make sure that an emlpoyee can't be entered twice. I appreciate that mobile numbers may change as too can email addresses so, maybe, you should add another piece of data which would prevent completely, any chance of a double entry for one person. eg national health number though for obvious reasons, not that. Maybe its simpler than that. what about the employees house number and the postcode together with their first name and last name?

    If the employee is already in the db, he/she will likely keep the details up to date and so they would always be accurate and prevent duplication.

    finally, lastUpdated needs to refer to another person who is also in the db. This column would therefore also be a foreign key.

    and lastly, I would suggest moving to all-lower-case for your col names. Much easier to help you if they are and an easier_to_remember_syntax.

    bazz
    Last edited by IBazz; Jan 27, 2009 at 17:28.

  23. #23
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Bazz - I'll have a look at that more in the week.

  24. #24
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just had a quick look over that, but wasn't sure about the multiple PKs in a table. Not sure why, but I always thought a table had just one, typically a unique ID, and so usually EmployerID etc.

    So in the EmployerContacts table you've suggested 4 - which kinda makes sense, as those are the ones you wanted duplicated.

    Although then I'm confused, as presumably you could have multiple Johns, or multiple Smiths?

    On a related note, using the same structure above, I have a page listing Employer and Contacts in the form :

    Employer 1
    Employer1.Contact1
    Employer1.Contact2
    Employer1.Contact3

    Employer 2
    Employer2.Contact1
    Employer2.Contact2

    etc

    Which uses the SQL :

    Code:
    SELECT * FROM EmployerContacts JOIN Employers ON EmployerContacts.EmployerID = Employers.EmployerID ORDER BY Employer, LastName
    And I have a search results page that lets you check boxes and returns matching employers, using the SQL :

    Code:
    SELECT * FROM Employers, EmployerProfiles WHERE Employers.EmployerID = EmployerProfiles.EmployerID AND EmployerProfiles.ProfileID IN(' . implode(',', $ckbox).') GROUP BY 
    Employers.EmployerID ORDER BY Employers.EmployerID
    (that's something someone helped out with about two years ago...)

    So I'm now trying to combine the to - ie have my results page, but also pull through the Contacts in the form above - but haven't had any joy.

    I've managed to get it working so that the correct Employers are shown. I presume I just need to somehow combine the two, and reference the EmployerContacts part in the second part, but haven't had any joy getting the Contacts for each Employer to show...

    If that makes sense?

    Thanks again.

  25. #25
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    OK I'll answer as best I can but one of the more experienced guys will, likely, be able to explain better than I, the reasons behind what I shall write.

    ... multiple PKs in a table ...So in the EmployerContacts table you've suggested 4 .
    Nope, I have suggested one PK which is a composite pk of four cols. The first name may not be unique (from record to record) as you say but there should be no records which match on all four cols.

    I'll suggest again that you should wait until we have your db sorted. Your queries are going to be different at that stage from how they are written now so there is not much point in doing them yet. I understand your eagerness - I've been there myself - but it is better to work our way through this 'chronologically'. You don't put your trousers/pants on before your underwear do you

    sorry if that was cheeky.

    And you need also to move away from the star selector (select * from tablename etc). if you add to your db in the future, the results form such a query may not work, if you add new columns to a table.

    And following on from my last post (with the tables), you should work out which relates to a profilie ~ the employer or the employee. Once you have decided upon that, you should add a column/series of cols to one table as appropriate.

    If you have a profile per employer (1 to 1 relationship) then the profile should all go in the employer table.

    If you have a profile which is and wil always be suitable for >1 employer, even if it amended at some point, you could add a FK for employer_id, to profiles. don't forget to add the FK constraint in that case.

    bazz


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
  •