mr_fern

If anyone uses vBulliten, maybe you can answer me this question. Maybe even Kayla can answer the question for me since surmunity uses it.

Anyhow, my question is:

How does vBulletin store birthdates in the database? 3 seperate columns for month, day, and year?

I'm considering changing my db schema to follow this kind of concept, which from my observation I believe is what vBulletin is currently doing. I have a query which finds the birthdays for a particular day, but it doesn't seem efficient since it can't use an index on the birthdate column.

__________________
Nobody doing nothing

David

`birthday` varchar(10) collate latin1_general_ci NOT NULL default '',

__________________
I'm a web designer that founded the company Asheboro Creative. You can follow me on Twitter, add me on Myspace, or read my articles on Asheboro Tech.

H

What's a value from that look like?

mr_fern

Ohhhhhh. Interesting.
I guess they store it as a string in some form of 'mm/dd[/yyyy]', where / could be any divider.

That makes sense. You can index the column, and then do a seaching using LIKE and use the column.
ex. LIKE 'mm%' for all the birthdays in a month, LIKE 'mm/dd%' for all the birthdays on a particular date.

That's useful. Perhaps I'll do 2 columns, 1 for birthday, and 1 for year.

Thanks David =D

__________________
Nobody doing nothing

H

I fail to see why they wouldn't store it as a date field. You can still apply the LIKE operator/clause.

1986-10-03

LIKE '%-10-03'

You could explode the value in PHP to get the year and do the math to determine the age.

David

I expected it as a date field as well. Figured it would be easier that way. And the value from that would be just like 0000-00-00.

__________________
I'm a web designer that founded the company Asheboro Creative. You can follow me on Twitter, add me on Myspace, or read my articles on Asheboro Tech.

mr_fern

It affects index usage, and the optionality.

As far as optionality goes, vb lets you choose to include your birth year or not. With a date column, that wouldn't be possible, you'd have to put a year in.

As far as index usage, you couldn't use an index on the birthday column for birthday searches. The query you supplied, you'd have to scan the entire table for those birthdays. While that may not seem bad with small amount of users, when your user base grows, the time it takes to perform that query grows exponentially.

While it would probably take a fraction of a second with say 1000 members, with 100,000, it would take a couple of seconds. By storing the date with the year last in a varchar, the index can be used, and even with 100,000 members, the query can still take only a franction of a second.

__________________
Nobody doing nothing

David

You need to write a book.

__________________
I'm a web designer that founded the company Asheboro Creative. You can follow me on Twitter, add me on Myspace, or read my articles on Asheboro Tech.

mr_fern

lol I'm too busy wondering what books I need to read to be writing my own.

I download a lot of pdfs and manuals relating to a lot of this stuff. I'm going to go to the book store and buy some more books soon. And I have my online bookmarks. Like http://www.oreillynet.com/linux/cmd/ , very good link. It's a list of linux commands from the Linux in a Nutshell book, which each command having a page with basic how to use info.

__________________
Nobody doing nothing