i must convince my boss 1 table is better than 3

[Archit3kt]

my boss wants me to make a database to manage a "new product development process".

every time we go through the process, we go through the same 63 steps. each step has an owner, a due date, a deliverable, a comments section, and a status. we have to follow the 63 steps in order, in 5 "phases".

I believe it would be most simple to make 1 table with fields like this:

step1_owner

step1_duedate

step1_deliverable

step1_comments

step1_status

step2_owner

step2_duedate

step2_deliverable

step2_comments

step2_status

step3_owner

....and so forth.

Each project would only be 1 record, and I would manage the "user experience/correct phase sequence" with various layouts showing only the fields for each phase, and navigation buttons that are scripted to check usernames for permission, and the contents of the fields to verify completeness before allowing navigation to the next phase (layout).

my boss does not like the idea of having to make 1 table with hundreds of fields, and multiple layouts. he is afraid of that. he would rather make 3 tables, one for "projects", which is linked to a "steps" table that has 63 records related to each 1 record in projects, and a "phase" table which is linked separately to the "steps" table that governs the completion statuses blah blah blah.....and he thinks this would be more simple because it reduces the number of fields from 1 table=700 fields to 3tables=30something fields.

he does not understand that this is a scripting NIGHTMARE. in his version he wants me to make a script to "add a new project" which will basically duplicate the last project and some of the field data. it will have to be hundreds of lines long, navigate all over the place, sorting records from the old project and setting some data into variables, then adding like 79 new records in 3 different tables, maintaining the right relationships, dropping the variables into the right records and fields..........WHEREAS, in a one table version, the script would instead be:

add new record

What I want to do, if I can do it in time, is make BOTH versions for him, so they appear exactly the same visually, and show him the difference in the scripting, and remind him that this is simply making them both the same as a baseline... when we want to add things like notifications of completion, reporting on how long each step took, etc., the 3 table version will be cripplingly complex.

is that the right thing to do?

in addition, the 3 table version is MUCH less secure....the user could enter any one of those three tables, and add new records via the built-in-buttons that are not related correctly to records in other tables, especially since some of the fields are set to add serial numbers on creation, and the user would not know what they are doing.

in a 1-table version, I have the user trapped in an iron-clad path they must follow, and there is only 1 kind of record to create.

[comment]

IMHO, your boss is right.

I am not quite sure why his approach needs to lead to a "scripting nightmare" or be any less secure.

[Rick Whitelaw]

Your boss is correct. Think a bit and I'm sure you'll see.

[Ocean West]

I can guarantee you that a single flat file is NOT the approach to this - I have just spend countless hours normalizing a flat file into proper structure - it now allows

for infinite expandability and does not require anyone to dive into schema to ADD one more step.

Following normal form to the degree where the complexity nears performance and practical application is the goal.

As far as security of the solution you can develop and lock down the layouts the user interacts with turning of features the UI or based on the persons login credentials.

[Archit3kt]

you guys are right! my boss is right....i did think about it a bit, and i do see. he is right, bless him, but not for the reason he thinks. it's not just about managing the number of fields, or the length of scripts, is it? neither of those factors alone should be reason to use a structure that limits the potential for expansion or development in the future.

a flat solution would actually make some permanent barriers that would be more difficult than the scripting i'll have to do for 3 tables in the beginning.

I thought I was thinking further ahead than him (which is true), but i was not thinking far enough ahead to see that he is actually right.

Thank you guys, i really needed other people to tell me i'm not right to begin to question my own logic. I appreciate it.

[comment]

it's not just about managing the number of fields, or the length of scripts, is it?

No, but the reduction in the number of required fields is a natural result of normalization - something your boss seems to have an intuitive knack for.