Another way/better way of designing this page/table?

1. I have a page with some fields like first name, last name, address, city, etc.

2. Some fields are required and some aren't.

3. On the page, I want to have 2 buttons; "Save for later" and "Save & Submit"

4. I created two tables with the same fields. In one table (for Save-for-Later"), I have the fields as nullable.
     In the real table (table for Save & Submit), I have the required fields as non-nullable and the non-required ones as nullable.

So, I have 2 tables. I wonder if this is a good design. If I want to display ALL the data (complete and incomplete) in a table, I have to go to two tables and get the data. Maybe I need a "complete" flag?

Maybe I just need one Save-For-Later table and have all the fields as nullable. When user clicks on "Save & Submit".... I insert the row from one table to the other?

Any ideas or is my orig design idea is good?
LVL 8
CamilliaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
So, I have 2 tables. I wonder if this is a good design. If I want to display ALL the data (complete and incomplete) in a table, I have to go to two tables and get the data. Maybe I need a "complete" flag?
it depends on how important is for those incomplete data ( after "Save for later" button was clicked" ) ?

if these data is crucial then you may put all data ( whether is for "Save for later" or "Save & Submit" ) into a single table, use a flag (like the field: complete) for completion indication.

if that's a scenario in which those data saved from "Save for later" will be purged after a certain period of time, OR in a situation that the rate of "Save for later" is significantly higher than the rate of "Save & Submit" then you may consider to put it on a separate table.
0
CamilliaAuthor Commented:
Thanks. If I put only use one table, then the "required" fields have to be nullable in the table. I think that's not a good design but it might be ok. I'll think about it more.
0
Scott PletcherSenior DBACommented:
First and foremost, a table design should have NOTHING to do with the screen(s) used for data entry.  The data itself is what should be modeled, not the current method of entering it.  (This is the main reason developers should never do db "design": because they model the interface/programming rather than the data!)

As to your specific q, you should have separate sets of tables (I'm virtually certain a proper data modeling would result in at least two tables).  You should not store "unreal"/tentative data with permanent data.  As you've noted, some values will not even be present until the data is finalized.  If those values end up being required key values in the actual tables, you wouldn't want to/be able to leave them empty even if you wanted to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Thanks, Scott. I created 2 tables.

I thought about having one table with the required fields as non-nullable and populating them with a predetermined "dummy data" if user clicks "Save for Later" but I don't think that's a good idea. I'll go with 2 tables...one for real data and one for when user saves for later.

I can pull data from both tables and have a column in the grid to show which records are completed and which ones aren't.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.