Building Tables
-
Hey guys...I guess I suck at building tables.
Anyway, I have this webform that consists of 7 parts. It's for a small claims department at a University.
Basically the users utilize the form to enter in data related to a claims case. So they need:
The school that wants to examine the claim
The College that is handling the claim
The building where the claim incident occured
The employer of the claimant
The physician who examined claimant
The claimant information(personal info)
And specific instructions on how to handle the claim
The parts of the forms are:
School Info
College Info
Building Info
Employer Info
Physician Info
Claimant Info
Specific Instructions
Naturually, or unnaturally, I created 7 tables based on each part of the form. I guess this is the wrong way to do it.
So I guess my question is...what would be the best way to design the table(s)?
Thanks!
M
-
Just put all fields that you have in 1 line and read Normalization to create your tables
Kindest Regards,Vasc
-
Hi
I would merge "Claimant Info" & "Specific instructions" because they are connected only with Claim Case.
Max
-
Please repost your current table definition.
-
And read this :
-
Thanks for the link, I'll print that out and read it over lunch.
Here are my current tables:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_school_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_school_Info]
GO
CREATE TABLE [dbo].[FG_school_Info] (
[SRschoolID] [int] IDENTITY (1, 1) NOT NULL ,
[SRschoolNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolCompany] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolBranch] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolPhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolFax] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolRegion] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolSupervisor] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolclaimType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschooldateOfLoss] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolinsured] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolclaimNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolpackaging] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolcontactPrefs] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolClinkID] [int] NULL ,
[SRrequestDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRrequestSubmitted] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_college_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_college_Info]
GO
CREATE TABLE [dbo].[FG_college_Info] (
[SRcollegeID] [int] NULL ,
[SRcollegeNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegePhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcopyOnReports] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeContact] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeInfo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_building_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_building_Info]
GO
CREATE TABLE [dbo].[FG_building_Info] (
[SRbuildingID] [int] NULL ,
[SRbuildingCompany] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingContact] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbuildingInfo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_Employer_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_Employer_Info]
GO
CREATE TABLE [dbo].[FG_Employer_Info] (
[SRemployerID] [int] NULL ,
[SRsubjectEmployer] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerPhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerContact] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRemployerInfo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_Physician_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_Physician_Info]
GO
CREATE TABLE [dbo].[FG_Physician_Info] (
[SRphysicianID] [int] NULL ,
[SRphysicianNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianPhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianContact] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRphysicianInfo] [char] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_Physician_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_Physician_Info]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_Specific_Instructions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_Specific_Instructions]
GO
CREATE TABLE [dbo].[FG_Specific_Instructions] (
[SRSpecInstID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRreason] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRobjective1] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRobjective2] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRobjective3] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbudgetAmount] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRsingleDaySurv] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRmultidaySurv] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRdueDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcomments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRbackgroundCHeck] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRhospitalCanvass] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRsceneInvestigation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRresidencyCheck] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRstatement] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRactivityCheck] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRwidowCheck] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRreferralBasisPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRreferralReasonsPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRsiuIndicatorPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcostPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRteamLeaderApprovalPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRmanagerApprovalPNI] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_Claimant_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_Claimant_Info]
GO
CREATE TABLE [dbo].[FG_Claimant_Info] (
[SRclaimantID] [int] NULL ,
[SRclaimantNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantNameMiddle] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantOffice] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantZip] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantPhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantDateOfBirth] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantSSN] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantRace] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantHairColor] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantHeightLow] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantHeightHigh] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantWeightLow] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantWeightHigh] [char] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantSex] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantMaritalStatus] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantSpouseName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantSpecialChar] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantOccupation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantHobbies] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantInjury] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantRestrictions] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantChildrenNum] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantChildrenAges] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantRepresented] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle1Make] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle2Make] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle1Tag] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle2Tag] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle1Model] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRclaimantVehicle2Model] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
/*
You need to establish the relationships between each table and then use a foreign key to
tie the two tables together.
Assuming that Colleges have schools you will need to add a FK to the Schools
you can use the RSCollegeID for the FK.
Relationship suggestions
you would need a table for each object ie.claim Type
Each table will need a FK to the objects it is related to
Colleges have schools
schools have buildings
Claimates have employers
Claimates have claims
claims have
locations building (if we know the building we can find the school and the college)
doctors
insurance companies
special instructions
claim type
This should get you started but there may be a better design depending on your business needs
HTH
Mike
[edit highlighted rows that do not belong in the school table. each table should only contain information relating to that table only.]
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_school_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_school_Info]
GO
CREATE TABLE [dbo].[FG_school_Info] (
[SRschoolID] [int] IDENTITY (1, 1) NOT NULL ,
[SRCollegeIDFK] [int],Not Null,--added FK to college
[SRschoolNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolCompany] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolBranch] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolPhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolFax] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolRegion] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolSupervisor] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
--claim information does not belong in this table, you will have to create a claims table
-- [SRschoolclaimType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
--[SRschooldateOfLoss] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolinsured] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [SRschoolclaimNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolpackaging] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolcontactPrefs] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRschoolClinkID] [int] NULL ,
-- [SRrequestDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- [SRrequestSubmitted] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FG_college_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FG_college_Info]
GO
CREATE TABLE [dbo].[FG_college_Info] (
[SRcollegeID] [int] NULL ,
[SRcollegeNameFirst] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeNameLast] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeAddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeCity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeState] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeZip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegePhone] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcopyOnReports] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeContact] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SRcollegeInfo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
Do shcools and colleges really have a first and last name.
Mike
-
magyar...if you want to follow the path of least resistance (meaning - use as much as you already have), then my suggestions would be:
1) Each of your tables - school, college etc.. should have a primary key. For you, it would be the easiest to make this an IDENTITY column (as remi said in the other post - don't anyone start on the "identity crises" business - I know it makes no sense in some of the tables (in fact - it's downright wrong) but we are going the easy route)...
2) Your claimant table should have a foreign key linking to each of these tables - meaning - it will have schoolFK, collegeFK, BuildingFK etc...
3) Your application should know when something is a duplicate - if I were you, I'd have drop-down boxes for each of the entities - eg: school - you have a drop-down box listing all the schools you have in your table - when the user selects a school name, the other "boxes" in your form get populated based on the selected name. If the name does not exist in your drop-down list, then the user fills in information for a new school and it gets a new ID....the idea being that the same school may be examining many claims - you don't want to keep duplicating that information in the school table.
The same should be done for all your other tables.
4) you should also choose all your datatypes with care - here're several pointers and guidelines:
a) why do you have your State as char(10) - what about North Dakota - Mississippi - etc...?!?! It would be much better to have them as char(2) and use the postal abbreviations or increase the length.
b) why is zip varchar(20) - if I understood you correctly from the other post, this is within the U.S and a small application - so none of the zip codes are ever going to exceed the zip + 4 length!
c) ditto for phones and faxes - even after you throw in extensions, you're still not going to exceed a max of about 22 characters - you should validate these in your form itself and allow the user to enter only one (chosen) way - be it (111) - 222 - 3333 or 1112223333 or (111)222-3333 - you pick one way and force the user to use that method only...
d) I'm curious about your SpecInstID - why have you declared it as char(10) - what did you have in mind for this column ?!
e) for claimantSex - char(6) would cover it - or even char(1) and have just an M or an F
f) claimantChildrenNum - change this to a tinyint - it's highly unlikely anyone will ever have more than 255 children so this is the perfect datatype for this column.
g) claimantchildrenages - never, never, never - store ages - store only the date of birth and store this as a date datatype - the children may be four years old today but 2 years later when the claim is finally processed, they will still be showing as 4 in the database...similarly for claimantdateofbirth - change this to a date datatype - so further down the road if you're asked to pull anything up by age/d.o.b etc..you won't have to do hairraising manipulations and conversions....
h) again - as mike pointed out - many of your columns need to be reshuffled - you need to look at each of the tables closely and identify which column belongs where...in fact, it would be great if you could break them down still further...blessing's advice to have a separate vehicle table is one such example...
like i said before - i'm a minimalist so i only use what i need....the other guideline is to use integers as often as you can get away with it!
5) Lastly I'm curious about several things:
a) as mike pointed out - do schools, colleges & buildings really have first and last names ?!?!
b) why do all the column names begin with SR
c) in the claimant table, what're heightlow - heighthigh and weightlow - weighthigh ?!?!
d) in the specific_instructions table, what is a widowcheck ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply