ansaurus

Question

What fields types do you suggest for this use case in my database?

Answer 1

+1 A:

int for ID
string for Course_Name

since you're using SQLS2K8 you can split DATE and TIME fields so..

Date for Opening_Month
Time for Opening_Hour

Eton B. 2010-08-06 20:07:08

Answer 2

+1 A:

varchar if the names can be ascii only, nvarchar for unicode names, date for month and time for time. The ones that look like this are the SQL Server types

SQLMenace 2010-08-06 20:10:07

Answer 3

A:

If you only need the month(not date or year) and the time, then you could use an int for the month, and a Time for the time. You could also use the DateTime instead and just work out a convention that ignores the date and year aspect of the date.

I would recommend nvarchar or varchar for the name. Use nvarchar if you anticipate there ever being a point in the future that you will need to support foreign languages that might contain unicode characters that are not supported by varchar. I would look at the longest name I anticipate needing, double it's length and then round up to the nearest 50. So if "Introduction to Partial Differential Equations" at 48 then I would make it varchar(100).

The purpose of making it so much larger is to allow for future values. If I only made it varchar(48) to accomdate the current largest value, I might later have a coursename that's larger than that, and thus would have to modify the database structure and the applications to accomdate this larger size! By making it varchar(100) we have plenty of room for larger course names that might occur in the future.

AaronLS 2010-08-06 21:14:03

That is incorrect! varchar(50) and nvarchar(50) both store 50 characters, but nvarchar will use 100 bytes per storage. Here is an example that stores 1 character, no need to make it nvarchar(2).....declare @n nvarchar(1) set @n = N'文'select @n. This is also a reason you can't do this declare @n nvarchar(5000) but you can do this declare @n varchar(5000)

SQLMenace 2010-08-06 23:59:14

@SQLMenace I know all this and did not state anything that contradicts it. Did you even read my post or are you just trolling? What is incorrect? Quote me where I make an incorrect statement please.

AaronLS 2010-08-09 13:52:58

@SQLMenace Maybe you are confused about why I doubled the length. The reason I do that is because to allow for future values that are larger than current values. "Introduction to Partial Differential Equations" may be the current longest value, but the application may encounter longer values in the future. Without knowing for sure what you will encounter, I simply take existing data, double the max length to get a good data size with plenty of room for larger values that may occur in the future. This is pretty common practice and I'm surprised you're not familiar with it.

AaronLS 2010-08-09 13:56:57

Looks like I misread your answer..I thought you meant doubling when using nvarchar........sorry..not trolling

SQLMenace 2010-08-09 14:20:35

Sounds good. I will edit to make more clear.

AaronLS 2010-08-09 15:00:08

ansaurus

tags:

views:

answers:

What fields types do you suggest for this use case in my database?

related questions