October 17, 2014 at 10:51 am
I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
Please advise. Thanks.
Steve
October 17, 2014 at 11:02 am
No one would be capable of give any help with such little information. You're asking for a DB design but that can be a really big project and you might do it wrong without the necessary experience and knowledge.
October 17, 2014 at 11:30 am
I would start here. http://www.sqlservercentral.com/stairway/72400/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2014 at 7:35 pm
Luis,
I want to thank you for taking the time to read my post. Let me address your questions.
Are you seriously taking the advice and code from someone from the internet without testing it?
Yes, I'll take the advise if it make sense. How do you know I will not test. Don't prejudge because you are assuming.
Do you at least understand it?
Yes, I do understand it. We have 2 different solutions at this time and I am simply looking for other approaches. That will help improve the current process.
Or can it easily kill your server?
I do not understand this but thanks for your feedback anyway.
October 19, 2014 at 6:32 pm
Hi scantoria,
I'm glad that you read the questions. They weren't intended specifically for you, they're part of my signature and appear on all my posts. You wouldn't believe the amount of people that trust blindly on code they get from the internet without understanding what it does.
I'm sorry that we can't help, but as I told you, it's a really wide topic and you didn't give a lot of information.
October 20, 2014 at 9:04 pm
Hey Luis,
Thanks for taking the time to reply. Let me see if I can better define what I am looking for.
Currently, our forms are stored in many tables with a forms table to track versioning. There are many unknowns on how we are currently storing our forms and reporting is cumbersome. I inherited a mess and a non-normalized database scheme.
The design I am looking at is:
Design # 1 (main form table and create individual tables)
CREATE TABLE tblFormMain (
"fm_id" INT IDENTITY(1,1) PRIMARY KEY,
"fm_name" NVARCHAR(20),
"fm_version" NVARCHAR(20),
"fm_areaOfStudy" NVARCHAR(20),
"fm_dateCreated" DATETIME
);
GO
CREATE TABLE tblFormSchoolApplication (
"fsa_id" INT IDENTITY(1,1) PRIMARY KEY,
"fsa_fname" NVARCHAR(20),
"fsa_mi" NVARCHAR(20),
"fsa_lname" NVARCHAR(20),
"fsa_gender" NVARCHAR(1),
"fsa_dob" DATETIME,
"fsa_student_ID" INT
"fsa_dateCreated" DATETIME,
FOREIGN KEY (fsa_student_ID)
REFERENCE tblStudentInfo(s_id)
);
GO
CREATE TABLE tblFormChangeOfAddress (
"fcoa_id" INT IDENTITY(1,1) PRIMARY KEY,
"fcoa_old_add1" NVARCHAR(20),
"fcoa_old_add2" NVARCHAR(20),
"fcoa_old_city" NVARCHAR(20),
"fcoa_old_st" NVARCHAR(1),
"fcoa_old_zip" NVARCHAR(5),
"fcoa_old_zip4" NVARCHAR(4),
"fcoa_old_county" NVARCHAR(20),
"fcoa_new_add1" NVARCHAR(20),
"fcoa_new_add2" NVARCHAR(20),
"fcoa_new_city" NVARCHAR(20),
"fcoa_new_st" NVARCHAR(1),
"fcoa_new_zip" NVARCHAR(5),
"fcoa_new_zip4" NVARCHAR(4),
"fcoa_new_county" NVARCHAR(20),
"fcoa_student_ID" INT
"fcoa_dateCreated" DATETIME,
FOREIGN KEY (fcoa_student_ID)
REFERENCE tblStudentInfo(s_id)
);
GO
-- And so on.
Design #2 (I am not sure how to extract form data from a BLOB.)
CREATE TABLE tblForms (
"f_id" INT IDENTITY(1,1) PRIMARY KEY,
"f_name" NVARCHAR(20),
"f_version" NVARCHAR(20),
"f_areaOfStudy" NVARCHAR(20),
"f_actual" VARCHAR(MAX),
"fm_dateCreated" DATETIME
);
GO
My end goal is to be able to use forms data for BI and maybe allow dynamic form creation from the UI.
Again, thank you for taking the time to look at my post.
Regards,
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply