Deleting a table
By Joe Gardiner Thursday, 25th March 2010
This guide will presume you have already created a table in your CatN vCluster database, or have existing tables created by a CMS install or something similar. For a guide to manually creating a database table, have a look here.[youtube video="_nvOSKMxe3g" align="left" author="by Joe Gardiner - CatN" bgcolor="#000000" color="#336699" hd="true" height="300" title="Deleting a table" width="500"]
read more...Posted in Guides, phpMyAdmin | No Comments »
Data Types
By Joe Gardiner Thursday, 25th March 2010
When creating a table in phpMyAdmin, there is a long list of data types available when naming your fields.
Here is a brief explanation of the different data types available and what the names mean!
Text Types
| Text Type | Description |
|---|---|
| CHAR() | A string of fixed length, with a maximum length of 255 characters. |
| VARCHAR() | A string that can vary in length, with a maximum length of 255 characters. Adding a number in the brackets will determine the string length to be used in the column. |
Note: CHAR()'s offer faster processing for the database as long as all the fields are the same length.
| TINYTEXT | A string with a maximum length of 255 characters. |
| TEXT | A string with a maximum length of 65535 characters. |
| MEDIUMTEXT | A string with a maximum length of 16777215 characters. |
| LONGTEXT | A string with a maximum length of 4294967295 characters. |
| BINARY | Fixed length binary data, with a maximum size of 8000 bytes. |
| VARBINARY | Variable length binary data, with a fixed length of 8000 bytes. |
Blobs are treated as binary strings and can store large amounts of information, but are processed much slower.
| TINYBLOB | A string with a maximum length of 255 characters. |
| BLOB | A string with a maximum length 65535 characters |
| MEDIUMBLOB | A string with a maximum length of 16777215 characters. |
| LONGBLOB | A string with a maximum length of 4294967295 characters. |
| ENUM | Is short for enumeration, and means that each column may have one of a list of predetermined values e.g. age brackets 18 - 25, 26 - 35, etc. |
| SET | Similar to ENUM, but each column may have more than one value from a predetermined list. |
Numeric Types
INT types have an option called UNSIGNED. Normally integers are between a negative and a positive number, but UNSIGNED will start the range at 0 instead of a negative number
| INT type | Normal | Unsigned |
|---|---|---|
| TINYINT | -128 to 127 | 0 to 255 |
| SMALLINT | -32768 to 32767 | 0 to 65535 |
| MEDIUMINT | -8388608 to 8388607 | 0 to 16777215 |
| INT | -2147483648 to 2147483647 | 0 to 4294967295 |
| BIGINT | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 |
| DOUBLE | A large number that has a floating decimal point. |
| DECIMAL | A DOUBLE that is stored as a string, not an INT, and has a fixed decimal point |
| FLOAT | A small nuber that has a floating decimal point |
| REAL | A single precision floating point decimal number. |
| BIT | An integer than can only take the values of 1, 0 or null, and is one bit in size. |
| BOOLEAN | A true or false data value, 1 or 0, on or off! |
| SERIAL | A four byte integer that automatically increases by 1 |
Date and Time
| Data Type | Description |
|---|---|
| DATE | Date that follows the format rules of: YYYY-MM-DD |
| DATETIME | Date and time that follows the format rules of: YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | Date and time that follows the format rules of: YYYYMMDDHHMMSS |
| TIME | Date and time that follows the format rules of: HH:MM:SS |
| YEAR | A year in two or four digit format, 58, or 1958 |
Posted in Guides, MySQL | No Comments »
Database Keys
By Joe Gardiner Thursday, 25th March 2010
Databases use keys to store, sort and retrieve records efficiently and quickly. There are many types of keys: primary keys, candidate keys, and foreign keys, in this guide I'll quickly go over the differences.
Primary Key
A Primary Key is a unique identifier for a record in a database table. It is important as it enables access to a particular record to be very quick, based on a single record using a single unique identifier, and by definition there can only be one instance of the Primary Key for each record.
Commonly used Primary Keys are driver license number, telephone number, or an automatically incrementing user or ID number.
Here are a couple of examples for you...
Good example...
In a library, a good unique identifier for a book would be the books ISBN (International Standard Books Number), as no two books share the ISBN. It would not be appropriate to use the books author as the Primary Key, as many authors share the same name, and one author may have written many books that are available in the Library.
Bad Example...
A UK postcode can be considered a bad Primary Key. It may seem like a good unique at first, however many people may live under the same post code, as a post code represents a small area, not a specific building.
Using Post Code's also limits the scope of your database to just creating records for UK based users, it is extremely unlikely that American users will have a UK post code!
Candidate Key
A Candidate key is the minimum number of attributes (also called fields, or columns) that are required to achieve uniqueness. Unlike the Primary Key, a table can have more than one Candidate Keys, however, the Primary Key will always be selected from one of the Candidate Keys.
For example... A relationship in a Business directory may be between Employee and (Name, Address, Dept). In this example it would be safe to assume the Employee is being uniquely identified by a combination of Name and Address which is therefore a candidate key.
Foreign Key
A foreign key represents the link between one, or a set of columns in one database table, to another column, or set of columns in a separate database table. The columns in the table referencing to another separate table must be the primary or candidate keys in the referenced table.
The values in the referencing table must appear in the referenced row of the second table, so data is linked together, and a child table can be formed of the parent referencing table, forming a subset of data in the referenced table.
A good example would be to imagine an accounts department that has an account database for invoices linking to a supplier database. Supplier details are kept in a separate database table with each supplier being given a 'supplier number' to identify it.
Each invoice in the account table also users the supplier number. The Supplier Number is the primary key in the Supplier Table, and the Foreign Key in the Invoices table is also the Supplier Number and is linked (referencing) to that primary key.
Posted in Guides, MySQL | No Comments »
Creating a table
By Joe Gardiner Wednesday, 24th March 2010
Login to the CatN Control Panel and access phpMyadmin. A guide for this is available here.
Inside the phpMyadmin dashboard the left sidebar displays the name of your database, and displays any tables created inside this database. If you have just purchased your vCluster and have not installed any CMS's, this list will be empty.
Now if you want to add new tables to the empty database, you simply click the database name in the sidebar to be taken to the add table form. read more...
Posted in Guides, phpMyAdmin | No Comments »
Accessing phpMyAdmin
By Joe Gardiner Wednesday, 24th March 2010
Logging in to phpMyAdmin is simple, and is possible through two locations of the CatN control panel.
The first thing to do is head over CatN.com, and login to the Control Panel.
You can either access phpMyadmin through the database section of a specific vCluster, or use the Tools sidebar menu and specify the vCluster in the following menu. read more...
Posted in CatN Control Panel, Guides | No Comments »