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" <JumpMan222@aol.com> wrote in message
news:3F066B10.5030909@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" <xueming@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 (andy@andyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
Space: disk usage analysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])

[Geoff Berrow]

Message-ID: <lacdgv4k4u9tu9b4dnv1r496b72naeb8aa@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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]

[Steven]

"Geoff Berrow" <$bl$@ckdog.co.uk> wrote in message
news:suqegvcdsjj33n61h1piqfgco989hmcsci@4ax.com...

> Message-ID: <lacdgv4k4u9tu9b4dnv1r496b72naeb8aa@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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]

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