type of tables for relationship between them

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

type of tables for relationship between them

Post by php12342005 »

questions:
1. I read an article on the internet which says:
type of tables must be created as innoDB for generating relationship between them, right?
what does innoDB and other types of tables mean?

2) types of field
what are differences between text, char(..), varChar(..)?

thanks
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Abt 2) - String Types
Difference is mainly Storage Requirements

CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 255
BLOB, TEXT L+2 bytes, where L < 2^16
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

If you want to use foreign keys with referential integrity, yes you must use InnoDB tables for both ends of the relationship.
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

Post by php12342005 »

hi,
thanks for reply.

1. ---------------------------------
further question about field type:
anjanesh wrote: CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 255
BLOB, TEXT L+2 bytes, where L < 2^16
if so, TEXT and VARCHAR are better than CHAR from size of storage's view.
For example, if user inputs "Hello boy" in a field.
bytes used varies with type of the field according to the input

(type, bytes used)
CHAR(50), 50
VARCHAR(50), 9+1=10
TEXT, 9+2=11

is my understanding above correct?
if yes, why people use CHAR rather than VARCHAR very often?

2. ---------------------------------
further question about table type:
question:
does InnoDB use more storages than default table type? - I guees so. why default type of a table is NOT InnoDB, what is its drawback?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

php12342005 wrote: if so, TEXT and VARCHAR are better than CHAR from size of storage's view.
For example, if user inputs "Hello boy" in a field.
bytes used varies with type of the field according to the input
Because with VARCHAR your dbms has to calculate the real length each time. Where with CHAR it has a fixed length and doesn't need to calculate the exact record length when navigating through the records...

So as usual, it's a choice between calculations <-> memory
php12342005 wrote: why default type of a table is NOT InnoDB, what is its drawback?
In my experience people that use mysql don't care about data integrity. That would explain why they choose myisam and don't care about referential integrity etc..
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

InnoDB takes more storage space, and I beleive and (at least used to, it might be better now) uses rather coarse grain locking which reduces preformence to gain partial ACID. (Most other ACID compliant databases uses a finer locking model which results in less blocking)
Post Reply