Data type

資料表欄位設計時，針對不同資料類型，建議的資料型態(data type)

Web site design and development process

• Information gathering: Research surveys
• Planning: Before you start to build a website, Content development strategy | Register domain name, Choose web hosting | Information architecture | Data model: Data type, Data flow | Documentation: Request For Proposal | Licensing
• Design: CSS tools, Free fonts, Free photos, Emoji & icons
• Testing & delivery: Usability test, check browser compatibility | Web testing: Web Ping, Software acceptance test plan | Promote your web
• Maintenance: Site backup & restore test, Software update (OS patch or CMS security update)
• Need help? Community, I need inspiration, Web design glossary

examples of data and suggested datatype

birth year

• data type: int
• ex: Range from 1905 ~ 2013 (108 yeas old) from the Sing-up form from outlook.com [Last visited: 2013-02-04]
• range/limit: 122 yeas old

file name

file name for Win FTFS ; Linux ex3 and ex4 file system types.

• varchar(255) If the files hosted by Windows server, you should also take the limit of PATH length into account besides the limit of filename length.

IP

IP(v4)

• varchar(15)
• ex: 255.255.255.255

range/limit

• IP range - Classless inter-domain routing (CIDR)
  • 12 or 24 bytes (IPv4 and IPv6 networks)
  • ex: 69.208.0.0/32

IPv6: 128位元長度，以16位元為一組，每組以冒號":"隔開，可以分為8組，每組以4位元十六進制方式表示

• • data type:
  • ex: 2001:0db8:85a3:08d3:1319:8a2e:0370:7344

網址

• data type: >= MySQL 5.0.3 use VARCHAR(2083); nvarchar(2083) for MS SQL
• 最大 URL 長度是在 Internet Explorer 中的 2,083 字元 for IE

Chinese name

• • 最長姓名有13個字
  • LINE姓名欄位長度限制: 15字(15個中文字或15英文字母)

unix timestamp

• bigint(10) ;
• ex: 1328664539
• range/limit:

學校代碼

School ID defined by MOE at Taiwan / 學校代碼

• integer: 4(university) ~ 6
• ex: 0001(國立政治大學)、373607(臺北市立華江國小)。前面可能有零。
• range/limit:

經緯度 (經度,緯度)

• DECIMAL(18,12) or FLOAT( 10, 6 )
• ex: 37.401724,-122.114646
• range/limit:

價錢/金額

• DECIMAL(10,2)
• ex:
• range/limit: 0.00 ~ 99999999.99

台灣縣市欄位值

台灣縣市欄位值 下拉式選單

$city = array("基隆市", "台北市", "新北市", "桃園縣", "新竹市", "新竹縣", "苗栗縣", "台中市", 
        "彰化縣", "南投縣", "雲林縣", "嘉義市", "嘉義縣", "台南市", "高雄市", "屏東縣", "台東縣", "花蓮縣", 
        "宜蘭縣", "澎湖縣", "金門縣", "連江縣");

密碼

• varchar at least eight (8) characters

membership

• Membership Providers

商品序號或商品條碼

• GTIN 全球交易品項號碼(Global Trade Item Number) 最長 14 位數
• EAN 國際商品條碼(International Article Number)，即前歐洲商品條碼 (European Article Number) and Japanese Article Number (JAN) 最長 13 位數
• UPC 通用產品代碼 (Universal Product Code) 最長 12 位數

網站專屬的商品序號

• ASIN 亞馬遜標準識別號碼 (Amazon Standard Identification Number) 由十個字元(字母或數字)組成
  • MySQL: VARCHAR(10)

書籍

• ISBN 國際標準書號(International Standard Book Number) 最長 13 位數

很長的文字

• MySQL: TEXT
• MsSQL: NVARCHAR(max)

 (left blank intentionally)
* data
** data type
** ex:
** range/limit:

comparision of datatypes in different database server

Storing Unicode text

• MySQL: varchar
• MS SQL 2008: nvarchar

DATETIME: ex: "2013-06-13 03:33:33"

• MS SQL 2008, MySQL is equivalent

TIMESTAMP: MS SQL 2008 and MySQL is NOT equivalent

• MS SQL 2008 ex: 0x00000000000007D3
• MySQL ex: 2013-06-13 03:33:33

tools

ER圖(entity-relationship diagram)

• MySQL :: MySQL Workbench 6.0: Notice the installation prerequisiteshttps://www.planetoid.info/images/Icon_exclaim.gif

參考其他資料表的結構設計

• DESCRIBE table_name;

references

further reading

• MySQL :: MySQL 5.0 Reference Manual :: 10 Data Types
• SQL Server 2005 Data Types (Database Engine) / 資料類型 (Database Engine)
• Oracle Data Types
• Industry Data Models