MySQL question.

[Steven]

Try to create a MySQL tables and don't know how to decide the type of some
fields:

Key: SSN - use int/bigint/char ?
PhoneNumber - int/bigint/char ...?

The reason I asked is that I got a space used exceed the limit with just one
row...

Thanks in advance

Regards,
-Steven

[Steven]

"Marcus" <Ju********@aol.com> wrote in message
news:3F**************@aol.com...

Steven wrote:

Try to create a MySQL tables and don't know how to decide the type of some fields:

Key: SSN - use int/bigint/char ?
PhoneNumber - int/bigint/char ...?

The reason I asked is that I got a space used exceed the limit with just one row...

Thanks in advance

Regards,
-Steven

Steven,

I would just use char for both... I have stored phone numbers and just
use char(12) for 000-000-0000. If you want to use an integer type, int
would be the smallest size that would fit a 9 digit SSN... using
UNSIGNED should give you a range up to 2^32-1. I believe you would need
a bigint for a 10 digit phone number... again use UNSIGNED, as that
should give you up to 2^64-1. Hope that helps.

Marcus

Thanks Marcus !

I have to use bigint for the phonenumber before I change it to char...

-Steven

[Andy Hassall]

On Sat, 05 Jul 2003 05:29:21 GMT, "Steven" <xu*****@comcast.net> wrote:

Try to create a MySQL tables and don't know how to decide the type of some
fields:

Key: SSN - use int/bigint/char ?
PhoneNumber - int/bigint/char ...?

The reason I asked is that I got a space used exceed the limit with just one
row...

Phone numbers are strings, not really numbers, so definitely use varchar.

Depends how you store them, but even if you don't have characters like (, ), -
and spaces in them, leading zeros are significant, so you don't want them
disappearing if you used a numeric datatype.

Don't know what the rules on SSNs are to say one way or the other, though.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Geoff Berrow]

Message-ID: <la********************************@4ax.com> from Andy Hassall
contained the following:

Phone numbers are strings, not really numbers, so definitely use varchar.

Depends how you store them, but even if you don't have characters like (, ), -
and spaces in them, leading zeros are significant, so you don't want them
disappearing if you used a numeric datatype.
My students always make this mistake. I usually ask them "could you use it
in a calculation?" If no, it's not a number.
Don't know what the rules on SSNs are to say one way or the other, though.

Same applies, I'd say.

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Steven]

"Geoff Berrow" <$b**@ckdog.co.uk> wrote in message
news:su********************************@4ax.com...

Message-ID: <la********************************@4ax.com> from Andy Hassall
contained the following:

Phone numbers are strings, not really numbers, so definitely use varchar.
Depends how you store them, but even if you don't have characters like (, ), -and spaces in them, leading zeros are significant, so you don't want them
disappearing if you used a numeric datatype.
My students always make this mistake. I usually ask them "could you use

it in a calculation?" If no, it's not a number.

Don't know what the rules on SSNs are to say one way or the other,

though.
Same applies, I'd say.

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Thank you both and that is a very good point !!

It took me almost two hours to figure out that I need bigint to store the
phone number 9727121049, if I use int, it always changed to 214..... which
I guess is the largest number for int. After I use bigint, it works fine,
but I don't feel that is right though :)

Thanks again.
-Steven