Re: ENUM() vs TINYINT

[shawn l.green]

On 9/21/2015 9:03 AM, Richard Reina wrote:

I have a column name quarter which I need to have 5 possible inputs; 1, 2,
3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
Hence, I am also thus considering ENUM('first', 'second', 'third',
'fourth', 'overtime') as the input will primarily be used in written
descriptions. Is this a wise or unwise way to design a table column?

Thanks



Depending on the specific sport (and level of competition), there may be 
more than one OT period. Do you really want to aggregate all of the OT 
stats into just one bucket?


It makes better sense to me to use a TINYINT for storage then for any 
values >=5 convert to "OT", "OT2", ...  unless it makes no difference 
for your purposes which period of extra play you might be in.


This would also allow you to easily query your stats for any rows where 
`quarter`>4 to see which games, if any, experienced any OT play at all.


You could do the same with ENUMS but then you would need a longer list 
of ENUMS (OT, OT2, OT3, ... ) to cover all the possibilities.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ENUM() vs TINYINT

[Jan Steinman]

> From: Richard Reina 
> 
> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?

I think it's a wise way to do things.

I use ENUMs a lot, whenever I'm choosing from a fixed set of a relatively small 
number of items that will not change frequently (or at all).

One other thing to consider is if this particular set of choices will be used 
elsewhere. If so, then consider using a TINYINT index into a different table 
that associates those indices (PK) with strings. Otherwise, I see no good 
reason to use TINYINT.

 You don't have to take insults personally. You can sidestep negative 
energy; you can look for the good in others and utilize that good, as different 
as it may be, to improve your point of view and to enlarge your perspective. -- 
Stephen R. Covey
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ENUM() vs TINYINT

[Basil Daoust]

I'm sure your enum is a fine option, but 0,1,2,3,4 would do the same thing.
And you could add a comment to the table to describe it if desired.

On Mon, Sep 21, 2015 at 8:03 AM, Richard Reina  wrote:

> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?
>
> Thanks
>

Re: ENUM vs TINYINT

[Brian Mansell]

Cemal,

I recall hearing a similar question mentioned in a previous email
thread. In fact, here it is:
http://groups.google.com/groups?hl=enlr=ie=UTF-8threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1

(thread is titled enum or tinyint)

I hope that helps! 

On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar [EMAIL PROTECTED] wrote:
 
 Hi all,
 
 I need a boolean column and at to this time I always used ENUM('Y','N')
 for this. I'am wondering that will there be a performance difference between
 using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to TINYINT
 column.
 
 Best Regards,
 Cemal Dalar a.k.a Jimmy
 System Administrator  Web Developer
 http://www.dalar.net
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ENUM vs TINYINT

[Tim Brody]

If you want to use as little space as possible use char(0) and null/not-null
for your boolean values:

CREATE TEMPORARY TABLE test_bool (i char(5), bool char(0));
INSERT INTO test_bool VALUES
('true',''),('false',null),('true',''),('true',''),('false',null);
SELECT * FROM test_bool WHERE bool is not Null;
SELECT * FROM test_bool WHERE bool is Null;

In a multi-column table that saves you 1 byte per row.

Regards,
Tim.

- Original Message - 
From: Brian Mansell [EMAIL PROTECTED]
To: Cemal Dalar [EMAIL PROTECTED]
Cc: Group MySQL List [EMAIL PROTECTED]
Sent: Friday, June 25, 2004 8:01 AM
Subject: Re: ENUM vs TINYINT


 Cemal,

 I recall hearing a similar question mentioned in a previous email
 thread. In fact, here it is:

http://groups.google.com/groups?hl=enlr=ie=UTF-8threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1

 (thread is titled enum or tinyint)

 I hope that helps!

 On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar [EMAIL PROTECTED] wrote:
 
  Hi all,
 
  I need a boolean column and at to this time I always used
ENUM('Y','N')
  for this. I'am wondering that will there be a performance difference
between
  using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to
TINYINT
  column.
 
  Best Regards,
  Cemal Dalar a.k.a Jimmy
  System Administrator  Web Developer
  http://www.dalar.net
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
 
 

 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]