Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    5

    Cool (fairly) basic design query..(relationships)

    Hi there,

    First post - found the site yesterday. Great resource.

    I am beginning work on a PHP/MySQL backend for a relatives small business.
    The business is a building company, doing maintenance jobs & renovations.

    Im a bit rusty with my design, having not undertaken something like this since uni, and have a few questions mainly about relationships.

    So far I have the following tables.

    Clients
    Employees
    Jobs
    quotes

    each job = one client, each client = many jobs
    each job = one employee, each employee = many jobs
    each quote = one job(or not, if the quote is declined and never becomes a job)
    each job = one quote (or not, if the job was undertaken without requiring a quote)

    quote table has the same relationship to clients & employees as job table also.

    if I have the job table with FK's for clientID & employeeID, is this sufficient, or do i need new tables like "clientJob" with only 2 fields clientID & jobID - this part I am unsure of(ie why these tables are required rather than FK's in the job table)

    My second design dilemma at present is the quoting - it seems silly to have a quote table that almost mimics the job table. (ie, the relationships will be duplicated), but I think that adding quote related fields to the job table will get very messy for searches later on..

    As I said im pretty rusty, and this isnt a paid job, I do this in my spare time after work, but I still would like to get it right conceptually before I start any scripts.

    Any thoughts? All replies appreciated

    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gigantor
    if I have the job table with FK's for clientID & employeeID, is this sufficient
    yes

    Quote Originally Posted by Gigantor
    I think that adding quote related fields to the job table will get very messy for searches later on..
    depends on the fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    5
    thanks

    what sort of situation would require the "relationship tables" I described?

    ie -

    client table
    --
    clientID
    clientName

    job table
    --
    jobID
    clientID
    jobName

    clientJob table
    --
    clientID
    jobID


    Cheers!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    suppose a job could be for more than one client? you would keep the clientJob table, and remove clientID from the job table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    5

    Thumbs up

    right, so these tables are used to facilitate many to many relationships, rather than needing to define uneccessary "customer 1, customer2" etc columns in the job table.

    thanks mate

    the next thing I am not sure of is the best way to structure the quoting/job system.

    a business that quotes its jobs, needs to record those quotes, to send to customers, keep for records, etc. The quotes might not become an actual job, or they may. Similarly, a job might not get quoted before it is commenced, if there is urgency etc.

    however, the quotes table, if it is defined seperately, contains alot of the same fields as the job table. all the same relationships are required, and a new quote-to-job relationship will be needed.

    I think the most clear and logical way to do it is to have them as seperate tables, it is less of a headache, but is this duplication of fields (2 very similar tables for 2 different purposes) "bad design"? should I be managing the quotes within the job table and defining the relationship between the 2 in the one table?

    this is more of a theoretical design question than an actual problem.

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Gigantor
    I think the most clear and logical way to do it is to have them as seperate tables, it is less of a headache, but is this duplication of fields (2 very similar tables for 2 different purposes) "bad design"? should I be managing the quotes within the job table and defining the relationship between the 2 in the one table?
    You're basically saying you could just have one table JobsAndQuotes and have a boolean field that determines whether it's a job or a quote.

    I think it's bad design because tables should have clear semantics. Jobs and quotes aren't the same thing.

    BTW, you get 10 billion cool guy points for the username.

  7. #7
    Join Date
    Jul 2007
    Posts
    5
    haha..thanks mate

    I am doing the quotes in a seperate table.

    Back to that earlier example of a table that exists purely for many to many relationships, how would that be created (from a syntax POV)

    if you have only 2 fields, clientid & jobid, jobid references jobid in job table, and clientid references client id in client table.

    a job can have many clients and a client can have many jobs.

    do both keys become the primary key, and both keys are individual foreign keys also, referencing the other table equivalents?

    Is that even allowed?

    or can i have 2 individual foreign keys and not define a primary key?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Gigantor
    do both keys become the primary key, and both keys are individual foreign keys also, referencing the other table equivalents?
    yes, and yes, respectively

    Quote Originally Posted by Gigantor
    or can i have 2 individual foreign keys and not define a primary key?
    you could, but why? the PK ensures that you cannot accidentally link the same client to the same job more than onece

    plus, the PK is an index, which makes searches on the left column efficient (usually you would also define a separate index on the right column)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    5
    ok sweet, thanks for your help

Posting Permissions

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