Data Storing, single or multiple columns

[littleOne]

I would like to ask: -

If you let a user select his/her birthdate with 3 selectors, and you need to calculate his/her age sometimes. Would you store all three values mm,dd,yyy into three separate columns or a single column and extract them when you need to calculate?

I'm concerning about speed.

Most people that I know would use single column, but extracting them for calculation would use additional server processing resources, right?

Is there any advantages and disadvantages?

Should I use 3 columns or single column?

[Ejaz]

Well, usually columns are use to store certain attributes. Here, DOB (Date of Birth) is attribute, but further subdividing it and storing it doesn't make much sense. Moreover, you may need to do alot of extra coding for different purposes, for example, you can add/substract 2 dates, but here either you'll have to do all that stuff yourself. Similarly, in different quries you might find it difficult to incorporate and also in reporting, again you'll have to go through the extra steps.

[erickwidya]

i go with single column, it more easier to manipulate..eventhough *maybe it required more server processing but we now using Fast Computer so it would not make different

[exterminator]

What is the problem storing it in one column when you already have the functions defined to work with dates as a whole? And next time, you will ask to add 6 or 7 columns to cater to the need of storing addresses...won't you? One for house number, one for floor, one for street, etc.. that is so not required to think upon. Regards.

[littleOne]

Well, usually columns are use to store certain attributes. Here, DOB (Date of Birth) is attribute, but further subdividing it and storing it doesn't make much sense. Moreover, you may need to do alot of extra coding for different purposes, for example, you can add/substract 2 dates, but here either you'll have to do all that stuff yourself. Similarly, in different quries you might find it difficult to incorporate and also in reporting, again you'll have to go through the extra steps.

That is true, and there are handy functions to calculate user's age so as long as I stored them as date.

I was thinking that...., I only need the yyyy attributes when calculating age and I have search engine too, sorry that I didn't mentioned that. If someone searches for age, and among records that reached thousands..... ???

Either I separate the yyyy field, or create another column for updated of users ages. Search engine will only has to match that one column.

But getting the updated of users ages on that one column is also another problem.

[littleOne]

i go with single column, it more easier to manipulate..eventhough *maybe it required more server processing but we now using Fast Computer so it would not make different

I wouldn't let my mind go easy with fast computer today. Some people are still with 56K bandwidth, and developers are still upgrading their codes with optimized performance. Every little resource saving is counting.

[littleOne]

What is the problem storing it in one column when you already have the functions defined to work with dates as a whole? And next time, you will ask to add 6 or 7 columns to cater to the need of storing addresses...won't you? One for house number, one for floor, one for street, etc.. that is so not required to think upon. Regards.

For address, I'm current using column: -
street 1
street 2
city
state
country
phone
email

Thanks for reminding me. I'll combine street 1 and street 2 into single. I'll have to let alone city, state and country as they are needed for sorting in page display.

[exterminator]

But getting the updated of users ages on that one column is also another problem.That is not an option. You cannot have an Age column unless you have a reference date for that which makes it more complex to calculate the actual ages. And how do you think that the age is simply the difference of the number of years? Are you rounding it off? Are you sure that later on you will not show the age in x years + y months + z days ? Is it always going to be in number of years? If yes, then to me it makes sense to make it a seperate field but if there is a rarest chance of 'no' then let is be a date field.

Then there is an YEAR() function, I guess, with SQL Server that gives you the year passed a date. You could use that as well. Then there is a DATEDIFF function as well. Have a look at this link - Date functions (http://www.informit.com/articles/article.asp?p=31453&rl=1).

I think you are probably thinking that a seperate column will enhance the retrieval of the years of particular record based on some "where" clause. But, IMO, you are not going to gain a lot out of the indexing that you would put on that column because there would be too many rows with the same value. Again, this is my opinion and I don't have figures to back them but this is my gut feeling based on some theoretical fundamentals.For address, I'm current using column: -
street 1
street 2
city
state
country
phone
email

Thanks for reminding me. I'll combine street 1 and street 2 into single. I'll have to let alone city, state and country as they are needed for sorting in page display.This is one place where the de-normalization of databases finds a very useful application. Otherwise in most cases (except for similar circumstance where you avoid normalization) you will want to have a normalized database. Regards.

[littleOne]

That is not an option. You cannot have an Age column unless you have a reference date for that which makes it more complex to calculate the actual ages. And how do you think that the age is simply the difference of the number of years? Are you rounding it off? Are you sure that later on you will not show the age in x years + y months + z days ? Is it always going to be in number of years? If yes, then to me it makes sense to make it a seperate field but if there is a rarest chance of 'no' then let is be a date field.

I'm aware of that. Record is static, calendar is flipping. It came across my mind to have this field, but couldn't make my way out.


Then there is an YEAR() function, I guess, with SQL Server that gives you the year passed a date. You could use that as well. Then there is a DATEDIFF function as well. Have a look at this link - Date functions (http://www.informit.com/articles/article.asp?p=31453&rl=1).

Currently I stored into single column, yyyy-mm-dd. I'm using PHP functions for age calculation. Thanks for the link. I'll see if I can find some upgrade to my existing.


I think you are probably thinking that a seperate column will enhance the retrieval of the years of particular record based on some "where" clause.

Exactly. My existing working fine. I'm currently cleaning up my codes and will upgrade wherever possible. I'm just wondering whether I'm in the right track and how others design theirs. Perhaps I worry too much.


Again, this is my opinion and I don't have figures to back them but this is my gut feeling based on some theoretical fundamentals.

I appreciate your opinion. I have a clearer mind now. Thanks a lot for your input. Thanks everyone.

[exterminator]

What you can do is make 2 test instances of that table and insert a reasonable amount of records into them (same number) and then check the execution plan of the queries (same query) on both the test tables (one with date field using Year() function and another with year as a separate column). For SQL Server refer to this article - SQL Server Query Execution Plan Analysis (http://www.sql-server-performance.com/query_execution_plan_analysis.asp). For Sybase, there is sp_showplan. I am not sure what you are using but look out for your db system about the execution plan/path. Let us know as well about what are you test figures. Regards.

[littleOne]

I can't conduct comparison testing right now. I'm concentrating on codes cleaning and site feature upgrade. Still a long way to go.
I have another question on performance: -

I'm using PHP and mySQL. Is is true that : -

database function is always faster than server scripting function?

[exterminator]

database function is always faster than server scripting function?Well, that depends! What kind of functions are you comparing? Regards.

**EDIT** In case you are even slightly referring to stored procedures, refer to the following quote:
Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements. A stored procedure is a precompiled executable object that contains one or more SQL statements. Hence you can replace your complex SQL statements with a single stored procedure. Since, stored procedures are precompiled objects they execute faster at the database server. Most of the time, stored procedures contain more than one command; in this case, the time to pass the individual commands to the database server from the program is saved. The database is issued just one command (to execute the stored procedure) and the DB server executes all the commands and returns the result in the end. Hence, the overall interaction time with the DB server reduces in a great deal. This can result in a huge optimization in case where the DB server is accessed via a slow network.The source to this is - Advantages of stored procedures (http://www.programmersheaven.com/2/FAQ-ADONET-Advantage-Of-Stored-Procedures). In addition to what's there - you also get the flexibility of writing the business logic in the stored procedures. The interface for using a stored procedure is simpler than that of a query built on the client side. However, in case of dynamically built queries in stored procedures, the performance is not good since it needs to re-compile that stored procedure each time. Read up on dynamic queries on the server side (in stored procedures) - there is a thread upon that here as well - the relevant post ->Setting variables using dynamic SQL (inside stored procedure) (http://www.codeguru.com/forum/showpost.php?p=1286411&postcount=2). If you have any specifics about something..lets hear it now. Hope this helps. Regards.

[erickwidya]

Some people are still with 56K bandwidth, and developers are still upgrading their codes with optimized performance. Every little resource saving is counting.but the process just took at ServerSide not ClientSide, beside using multiple column using more than one SQL statement to update those table (using more memory right?)
about Developer, it only upgrading their SQL syntax to make an efficient Query but it based on the DBA's experience like designed Dataase (the Table, Query, choose PK, FK, index, etc)

exterminator has put great opinion and try to read about Normalization and Database related theory

PS : this also just my opinion :D

good luck