Need Guidance on Data Types and Lengths.
-
I need some guidance on choosing data types and lengths for columns in my database.
I have columns for names (First, Middle, Last, and Suffix), dates, counters, images, including titles and captions, text, and Booleans. Since these are all common sorts of data, my hunch is that best practices or rules of thumb exist for them. I have a vague recollection of reading that Unicode character types are better because they will accommodate any character, but they take more space. In my case, space won’t matter because my database will be small; but if there is a reason to choose char vs nchar please help me to understand how to make that choice.
Here are my ideas:
· Names, titles, captions, etc. will be nvarchar(n).
· Counters will be smallint as will my table primary keys, which will have IDENTITY attributes.
· Dates will be date which I understand to be stored as "yyyy-mm-dd".
· Biographies and such will be text (because I think 8000 characters is enough)
. Booleans will be bit.
I don’t know what to do with images. From what I’ve read, varbinary(n) is recommended for type.
Images will be in a separate table (or tables) and referenced with links. My guess is that columns containing those links should be varchar(n).
Lengths are a question. Are there rules of thumb or best practices for first, middle, last name lengths? What about for varbinary images? . . . and links (URLs and local pathnames)? Any rules of thumb for image and caption lengths?
I'm sure there are things I haven't considered; so please make suggestions. If you know of a good article on the subject, please share it. I'd rather do things well to start with and save time fixing things later.
Be kind. Be calm. Be generous. Behave.
-
- I avoid images in SQLServer and store their file system path in the database. ( devs can opt to e.g. use the pk of the person as basis for the filename on the designated file system )
- We rarely use smallint for primary key columns as 32,767 can be reached very fast and modifying a pk column also results in modification of all child objects!
- avoid using "external" column values for primary key ( you don't want to have to modify your whole data system due to an external party modifying thier identifier length or whatever ) and don't store such columns in a numeric data type ( for the same reason )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Strong suggestion, don't store the images in the database. Can you? Yes. VARBINARY(MAX) would be the way to go. However, it really negatively impacts a whole slew of things from backups to consistency checks, let alone coding, to put huge image files into the database. You can do it, but you shouldn't.
Just wanted to pile on that a bit. Otherwise, I agree with Johan.
As to the name column limits, I generally default to 50 when I don't have any other direction on it. I would put a limit on, don't make everything MAX. There are a bunch of reasons, but not the least is, it can make SQL Injection attacks more likely if you have unlimited character storage everywhere. It's harder to enforce string limits when there isn't one. Know what I mean? But there are also other reasons, internals, etc., to set string limits.
As to Unicode, If you need kanji & other stuff along those lines, sure. If not, I'd say stay out of it. Reason being, your columns are half the size. Now, I hear you when you say storage isn't a problem. But, I'm talking performance. You will only get half the values on a given page of an index when using NVARCHAR over VARCHAR. That's why you should avoid it if you don't need it.
Hope any of this is helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you, Gentlemen.
That's good information. I smacked my forehead over the images suggestions. I had planned on having links and pathnames in tables and just sort of assumed I need to put the images there, too. I'm thinking like the old flat-file programmer that I was 50 years ago.
I appreciate the Unicode caution, too. I'm sure that will prevent future headaches.
Be kind. Be calm. Be generous. Behave.
- Grant Fritchey wrote:
Strong suggestion, don't store the images in the database. Can you? Yes. VARBINARY(MAX) would be the way to go. However, it really negatively impacts a whole slew of things from backups to consistency checks, let alone coding, to put huge image files into the database. You can do it, but you shouldn't.
Like a lot of things, I think it depends. A lot of third party applications put images in the database due to:
- File Drift - where other users/processes move or delete files.
- File Corruption - DBAs with their consistancy checks reduce the chance of file corruption.
- Transaction Control - Easier when everything is in the database.
How efficiently the database engine is being used is not the only thing to consider during the design.
- Ken McKelvey wrote:Grant Fritchey wrote:
Strong suggestion, don't store the images in the database. Can you? Yes. VARBINARY(MAX) would be the way to go. However, it really negatively impacts a whole slew of things from backups to consistency checks, let alone coding, to put huge image files into the database. You can do it, but you shouldn't.
Like a lot of things, I think it depends. A lot of third party applications put images in the database due to:
- File Drift - where other users/processes move or delete files.
- File Corruption - DBAs with their consistancy checks reduce the chance of file corruption.
- Transaction Control - Easier when everything is in the database.
How efficiently the database engine is being used is not the only thing to consider during the design.
The fact that you don't put those in the database, doesn't mean you don't need to take controle over the file system where you plan to put them.
Security is key
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you, Gentlemen!
Be kind. Be calm. Be generous. Behave.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply