sql server - Is there a legitimate reason for using so many varchar fields? (MS SQL DB) -
i'm working on data migration old ibm universe-based system new enterprise-level data-information management system, , learning database design in process.
i took @ back-end database structure of new system (it's ms sql db, 100 tables), , find things pretty odd. don't know if inexperience reason think , standard practice, or if these oddities bad database/application design.
for example:
- some date fields varchar(20)
- fields store measurements varchar(50), rather decimal , enum store units of measurement
- isbn 10 & 13 number fields varchar(50)
- some look-up id foreign keys varchar(100), though actual look-up table primary key int
- some fields varchar(0)
- additional separate fields storing month & year, each of varchar(250) - i don't know kind of design decision require maximum of 250 characters year, unless went overkill on y2k compliancy, or decided use seconds since beginning of universe store datetime
and plenty of others. db looks more half varchar fields.
i should mention varchar fields in db n-varchar - it's unicode, fields store numbers.
is there legitimate argument using many varchar fields could best option, in circumstances? (flexibility... maybe...?)
some date fields varchar(20)
this 1 of things in trouble in future, can have invalid dates , can't normal date arithmetic.
some look-up id foreign keys varchar(100), though actual look-up table primary key int
this bad because conversions when join , make slower
store decimals decimals...sooner or later bad data in there , classic case of gigo (garbage in garbage out)
also using nvarchar store numbers insane, doubled storage needed store numbers, store less rows per page , need more io bring same number of rows if had used regular varchars or integers
Comments
Post a Comment