Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2016
    Posts
    15

    Help with consultancy-candidate-interviewer db design

    hi Experts,

    hope I get some help with this:

    I want to design database for candidate-interviewer. Its my own requirement and trying to move things from excel to database. Am still learning basics and hope You all help me.

    1. Each Consultancy will have many Candidates
    2. Each Candidate will be interviewed by one or many Interviewers (in my case Interviewers are nothing but employees)

    What are the key points I should keep in mind to design this type of db and how to make it more generic?

    How many tables should I create and how to relate each entity?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    normalisation is alwasy a good foundation
    understand what you are trying to achieve, and what the expected life of the project is. your design shoudl be flexible eniugh to adapt to developing circumstances
    good understanding of database concepts also helps
    go through the normalisation process
    sling soem test data at the design
    review the design

    ..and repeat untill you meet the use cases that formk part of the original design brief

    Q:how many tables shoudl you create
    A: as many as needed to meet the design brief

    Q:how to relate each entity
    A:using the well founded concepts of relational integrity through referencing primary keys, stored as foreign keys in the 'child' table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2016
    Posts
    15
    Hi Healdem,

    Thanks for reply. Am having problem with normalization itself. Can You please suggest as a beginner what are the things I should do? Or if You can tell me Your approach in building tables with respect to my scenario, I can follow and understand things.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    have a look at
    http://r937.com/relational.html
    http://www.tonymarston.net/php-mysql...se-design.html
    or even
    http://www.informationqualitysolutio...tionPoster.pdf

    go through the normalisation process. take all the stuff you need or think you need in your system
    sift it so that the actual values appear in one entity

    ferinstance a customer order requires tables for:-
    customers (details of customers, naem address, contact details)
    products (details of prodcuts you sell
    stock (may actuallybe modelled in many tables depends on your design
    orders (details of tghe order header (stuff that is common to the order (eg their roder number, the date, delivery date, who laced the order etc...)
    order details (the products ordered byt he custoemr as part of a specific order, injcludes price, quantity


    if you see repeating information in nore than ne tabel thats a good sign you have not normalised design
    if you see things like address1,2,3 or tel1,tel2,tel3 thats an indication the design isn't normalised
    but asoemtimes you may see stuff that looks the same (eg order details, why woudl you copy the price the product description as part of an orderr, because at the time the order is placed it ceases to be just a product it becomes that customers product. so you need to record the actual price quoted for the product at the time the order is placed, so your customer order price reflects the invocie reflect s the payment. otherwise you coudl end up with 2016 prioces being retrospectiveley applied to historic invoices

    will I go through an design soem tables for you... no
    you need to go through the normalisation process and understand the concepts. there are no short cuts. most db practioners skip the (early) stages of normalisation as they spot the trends subconciously. but if you are to gain any benefit from designing thsi yourself you have to design it for yourself. peoiple here will help, peoipel here will pointint he direction but I doubt they will do. I won't 'do'. frankly I can't decide if you are trying or passing off your homework, thats not a criticism but I don't do homework. homework is set to make certain you understand the process. so make an effort to read up and apply normalisation techniques
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2016
    Posts
    15
    Hi Healdem,

    Thank You so much for Your valuable reply and prompt response. I really appreciate the help You providing.

    ok I will do one thing. I will go through the links that You provided and I will build tables or design on my own. Later will post it here in the same thread and I want Your opinion and direction so that am in right track.

    Thanks,
    Richa

  6. #6
    Join Date
    Feb 2016
    Posts
    15
    Hi Healdem,

    I tried my best to understand the concepts (still am confused in few of them). Here is the requirement I put it in statement and generating logical diagram out of it. Please find the below points and logical diagram. Please let me know if the design is right or do I have to make some changes.

    Requirements:
    1 One or More Employee takes Interview of one Candidate at a time
    2 There will be multiple rounds of Interviews
    3 Each round will have status either Selected or Rejected
    4 Each round may be on the same date or different date
    5 Candidate comes from Consultancy
    6 One Candidate may have one or more consultancies
    7 One Consultancy can have one or more Candidates
    8 Candidate cant appear for interview for next three months if rejected in final round

    Logical Diagram:
    Click image for larger version. 

Name:	int.jpg 
Views:	9 
Size:	79.1 KB 
ID:	16913

    Please help me if I get it right or please correct me if am wrong so that I can learn from my mistakes.

    Regards,
    Richa

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    thinks of things that own other things

    or something has many of other things

    a ferisntance
    on order may have one or more different products. an order must have a customer, but a custoemr may have more than one order.
    so customer is part of an order, not the other way round
    unless you have one product per order then an order has many products, indcating that there should be a sub stable containing all products in that order. that also infers that there shoudl be a table for products and the PK of products is a fk in order details

    so revisit your model


    there are also someother issues


    can an employee have more than one interview, in which case where do you think the details that an employee was interviewed should take place

    what differentiates an employee from a candidate

    why would candidate X be a better prospect for vacancy 1 than candidate Y. unless of course you are going for the traditional ITR recruitment approach of who gives a stuiff lets just get a piece of warm b looded meat into a role so we can generate some fees off them.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2016
    Posts
    15
    Hi Healdem,

    Thanks and now am more confused that when I was developing the model. Its so frustrating on how database design looks so complicated even for simple scenarios.

    I googled, ITR recruitment data model and I got this. I think I will be very bad designer for database and inspite of reading tons of books and going thru video tutorials, am still so confused on how many tables do I need for my requirement and how to relate them.

    Please help.

    Regards,
    Richa

  9. #9
    Join Date
    May 2016
    Posts
    89
    Hi,

    For modeling correctly a db (
    See example on this dbforums post here),


    1. build a conceptual data model (CDM). The Logical data model is very simple then to generate. With tools, the LDM is automatically built as well as the SQL script for creating the DB
    2. Use dedicated tools which build CDM not only LDM like :
      • Power*AMC (excellent but expensive)
      • JMERISE, excellent freeware here


    And of course apply at least the 3 first Normal forms (NF)
    Last edited by informer; 06-28-16 at 06:28.

Posting Permissions

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