Review my DB design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Review my DB design

Post by Draco_03 »

Hi all been a long time.
Nyways i'm designing a databse.

So basically I have to keep track of potential clients.
The compagny have differnet products.

Missionїcode] Each employees would have a username/password, giving them access to the database. Everyone could see all potential clients, but every clients would be "assigned" to a sales person.

Each time a client request information from the website, his info is uploaded on the database and this client get an automated email with a link. On this link, there will be all information requested.

Once the request has been placed by the client, he would apears in the "to call" section* and the date he requested it would be saved..

Each conversation with to client would be archived. And in case of a follow up (put in date to call), it would automatically get updated in the task of the logged in user.

A report could be produced on any given field and a summary could be available.

*This brings a question, when a client request info he's not assigned to anyone. So that means there should be a "general tasks" where everyone can see, and a personal task, whereonly the users logged in could get access to.ї/code]

Missions Objectivesїcode]We need to maintain complete clients informations
We need to keep track of all discussions made with that client
We need to create different users with a username/password
We need to assign personal task to users
We need to have a general task for unassigned clients
We need to keep track of all appointment madeї/code]

Okay First if I'm missing something here tell me.

next is the table structure

їb]їu]Clientї/u]ї/b]
client_id(primary key)
client_interest
client_fname
client_lname
client_country*(foreign key)
client_province*(foreign key)
client_city
client_adress
client_postal
client_compagny
client_phone
client_phone2
client_cell
client_fax
client_email
client_heard*(foreign key)
client_profession*(foreign key)
app_id(foreign key)
Comm_id(foreign Key)
client_prodown**
client_rating**

їb]їu]Appointmentsї/u]ї/b]
app_id(primary Key)
app_date
app_location
app_desc

їb]їu]Communicationsї/u]ї/b]
Comm_id(primary key)
Comm_date
Comm_return
Comm_desc
Comm_list*

їb]їu]Taskї/u]ї/b]
this one is tricky not sure since i get personal task and genereal ones
I could make 1 table for їb]eachї/b] but it will be redundant data.
help here would be appreciated.

їb]їu]Usersї/u]ї/b]
Usr_name
Usr_pass
I'm missing something it seems....

*means that it will be populated by another table
ie : country will be a dropdown with differents country to choose from so there will be a table Country with country as a field (that would link both theses tables) or again Comm_list would be populated by a field in Table list containg different things you do often (reply email) that would automaticaly write it in description box instead of writing reply email over and over

**these field would be only available to users (the client can't upload his own rating :))

I'm at this point npow i'm reviewing the fields and so. While i'm doing that any help would be appreciated :)

Thx guys (and girls)[/u]
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

Anyone care to help here ?
err /bump
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

since there are 2 (current anyway), categories of Tasks, I'd think about having a table for those categories if there's a chance of adding more in the future.
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

Thank you feyd.

So 2 table

General_tasks

Personal_tasks

And the rest is good ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I was suggesting set up more like:

Task Categories
category_id
category_name

Tasks
task_id
task_category_id
task_user_id
....
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

..Mhh yep :)
thank you
Post Reply