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]

Re: MySQL question.


"Marcus" <JumpMan222@aol .com> wrote in message
news:3F066B10.5 030909@aol.com. ..[color=blue]
> Steven wrote:[color=green]
> > Try to create a MySQL tables and don't know how to decide the type of[/color][/color]
some[color=blue][color=green]
> > 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[/color][/color]
one[color=blue][color=green]
> > row...
> >
> > Thanks in advance
> >
> > Regards,
> > -Steven
> >
> >[/color]
>
> 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
>
>[/color]

Thanks Marcus !

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

-Steven

[Andy Hassall]

Re: MySQL question.

On Sat, 05 Jul 2003 05:29:21 GMT, "Steven" <xueming@comcas t.net> wrote:
[color=blue]
>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...[/color]

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) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Geoff Berrow]

Re: MySQL question.

Message-ID: <lacdgv4k4u9tu9 b4dnv1r496b72na eb8aa@4ax.com> from Andy Hassall
contained the following:
[color=blue]
> 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.[/color]

My students always make this mistake. I usually ask them "could you use it
in a calculation?" If no, it's not a number.[color=blue]
>
> Don't know what the rules on SSNs are to say one way or the other, though.[/color]

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]

Re: MySQL question.


"Geoff Berrow" <$bl$@ckdog.co. uk> wrote in message
news:suqegvcdsj j33n61h1piqfgco 989hmcsci@4ax.c om...[color=blue]
> Message-ID: <lacdgv4k4u9tu9 b4dnv1r496b72na eb8aa@4ax.com> from Andy Hassall
> contained the following:
>[color=green]
> > Phone numbers are strings, not really numbers, so definitely use[/color][/color]
varchar.[color=blue][color=green]
> >
> > Depends how you store them, but even if you don't have characters like[/color][/color]
(, ), -[color=blue][color=green]
> >and spaces in them, leading zeros are significant, so you don't want them
> >disappearing if you used a numeric datatype.[/color]
>
> My students always make this mistake. I usually ask them "could you use[/color]
it[color=blue]
> in a calculation?" If no, it's not a number.[color=green]
> >
> > Don't know what the rules on SSNs are to say one way or the other,[/color][/color]
though.[color=blue]
>
> 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/[/color]

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