Site Architecture help - need to define needed DB's

[Gabriel_Haukness]

I don't know where to post this post, but I am defining the scope of a site and want to ensure I build the correct number of databases. Here is what the site needs:

 

The overall site is an interpretation agency that has the client request a job and the job gets assigned to an interpreter of the appropriate language and then calculates fees:

 

A user portal where they can store and update their profile. They would be a client, interpreter or administrator. Ability to request a job and then modify or assign that job to an interpreter. Interpreters may also search the jobs. Ability for the user profile to display upcoming jobs from the calendar.Different job types (phone, in-person, electronic) and corresponding pay rates.Invoice calculator and delivery system.Ability for administrators to manage any user, job or calendar item.

A user portal where they can store and update their profile. They would be a client, interpreter or administrator.

 

Ability to request a job and then modify or assign that job to an interpreter. Interpreters may also search the jobs.

 

Ability for the user profile to display upcoming jobs from the calendar.

 

Different job types (phone, in-person, electronic) and corresponding pay rates.

 

Invoice calculator and delivery system.

 

Ability for administrators to manage any user, job or calendar item.

 

I am thinking the databases needed on the back end would be:

 

user DB [id, user type, languages, user name, password, e-mail, agency, street address, state / province, zip / postal code, country, ]

 

job DB [id, job name, language, type, date requested, time requested, time zone, street address, state / province, zip / postal code, country, completion status, assigned to:, requester, ]

 

calendar db[id, job name, start time, end time, date]

 

invoice db[job name, date completed, total] //or would I just query the job DB to make a gridview for the user?

 

I guess I am hitting a sticking point on the calendar DB and availability dates for users - users need to not be able to be assigned to a job if they are already assigned. How would I plan this out?

 

 

 

 

 

[phonydream]

If you know what type of database you are running, then your question might be better answered in that forum. MySQL, PostgreSQL, MSSQL etc.

 

BTW, I think you mean 'table' where you say 'database'.

[ignace]

A great help with any project is textual analysis and an ERD (entity-relationship diagram). Your text after stripping out all none db related material:

 

- the client request a job and the job gets assigned to an interpreter of the appropriate language

- They would be a client, interpreter or administrator.

- Different job types (phone, in-person, electronic).

 

I got:

 

-- code refers to a 2-letter country code http://www.modemsite.com/56k/_ccodes.asp
language (id, name, code)

-- polyglot means a person who speaks many languages
-- 'speak' indicates how well a person speaks that language 3 out of 5 for example where 5 is perfect or native speaker
polyglot (language_id, user_id, speak)

user (group_id, employee_of, street_address, ..)

group

job (language_id, requested_by, assigned_to, deadline)

job_type (id, name)

-- pph is pay per hour
-- i put pph here because depending on the client they may charge more or less per hour
invoice (job_id, client_id, hours, pph)

-- company where an interpreter works
agency (id, name, street_address, ..)

[Gabriel_Haukness]

Correct phony dream - I meant tables. I was still stuck in using "client-speak" I get blank stares when I start swapping out tables for DB's in the vernacular. And I dont know what type of DB I will be using as they are still getting hosting. Hopefully MySql and not MSSql.

 

ignace - I did make a flowchart. I suppose that would have helped. But I like your tables and columns and think you covered more contingencies than I anticipated.

 

 

 

[attachment deleted by admin]

[ignace]

The best would have been if you had defined your entire application in clear business rules both in terms of your application as your database.

 

http://en.wikipedia.org/wiki/Business_rule