Jump to content

Looking for a little help - logic/design process


awpti

Recommended Posts

Here's the overall situation.

 

I've been doing hobbby-development for a number of years (almost 10!) and have finally accepted an actual job developing a sort of Project Management tool (with a very narrowly defined set of features).

 

I have no problem putting the app together along with a database, but I've never been a -great- database designer. I'd love some input on this and any pointers to improve my own skills as much as possible.

 

This is the current design.

 

CREATE TABLE s3pm_employees (
`id` int not null primary key auto_increment,
`role_id` tinyint,
`username` char(32),
`password` char(40),
`first_name` char(32),
`last_name` char(32),
`email` char(128),
`cell_number` char(30),
`home_number` char(30),
`total_completed` int,
`skillsets` tinytext  # comma seperated list of skillsets (id)? SELECT... 
                       #s3pm_skillsets.. WHERE s3pm_skills.id IN ($meh->skillsets?)
);

CREATE TABLE s3pm_skillsets (
`id` int not null primary key auto_increment,
`skill_name` char(32) # Just what it sounds like, text representation of a skill
); 

CREATE TABLE s3pm_tickets (
`id` int not null primary key auto_increment,
`creator_id` int, #s3pm_clients.id
`employee_id` int default 0, #s3pm_employees.id
`request_type` char(32), # This may be an ever-changing. Another related table?
`files_attached` int, # 
`iterations` int default 10, # we subtract from this after each employee response
`status` enum('Closed', 'Open', 'Admin-Review')
);

CREATE TABLE s3pm_files (
`id` int not null primary key auto_increment,
`ticket_id` int, #s3mp_tickets.id
`file_type` char(3), #Probably only *imagetypes*, .txt, .doc, .pdf - ENUM?
);


CREATE TABLE s3pm_responses (
`id` int not null primary key auto_increment,
`ticket_id` int, #s3pm_tickets.id
`response` text
);


CREATE TABLE s3pm_clients (
`id` int not null primary key auto_increment,
`username` char(32),
`password` char(40),
`first_name` char(32),
`last_name` char(32),
`email` char(128),
`cell_number` char(30),
`home_number` char(30)
);

 

Have I done anything blatantly stupid?

 

I'm trying to avoid any gotchas before they.. get me.

 

Am I looking at any huge headaches if I eventually need to extend this in some small manner? (Probably never happen, but I may take this codebase and build another app out of it)

 

I'd really appreciate any thoughts on this.

I'd certainly replace that comma-delim list of employee skillsets with a table

 

[pre]

employee          emp_skills            skillset

---------        -----------            -----------

emp_id  ---+    id              +---- skill_id

role_id    +---  emp_id          |    skill_name

username          skill_id    -----+

etc

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.