awpti Posted January 26, 2008 Share Posted January 26, 2008 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. Link to comment https://forums.phpfreaks.com/topic/87873-looking-for-a-little-help-logicdesign-process/ Share on other sites More sharing options...
Barand Posted January 26, 2008 Share Posted January 26, 2008 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 Link to comment https://forums.phpfreaks.com/topic/87873-looking-for-a-little-help-logicdesign-process/#findComment-449906 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.