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

yes

depends on the fields

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!

suppose a job could be for more than one client? you would keep the clientJob table, and remove clientID from the job table

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.

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.

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?

yes, and yes, respectively

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)

ok sweet, thanks for your help