Hi all.

I am building up a DB to store user information and so far two questions has arised.

Question 1

one thing that I was wondering is if it is smarter/quicker for the DB-server to store information that go under the same category as an array or split this information into several fields.

For example.
When i store user phone numbers.
Atm I have three fields called
phone_home
phone_home
phone_cell

The other option would be to do it like this.
phonenumber

The array would look something like "+1 800 023 2345, +1 555 344 633, +1 345 523 542"
and then call this with SELECT * FROM phonenumber and later on split the array with PHP to grab the wanted information.

Question 2

This is a more SQL oriented question. Most of the fields in the DB are requierd fields, the user will have to fill them in if they want to register.
The thing I was wondering if the value of "NOT NULL" has any affect on this? I have tried to really understand what NOT NULL does but so far i haven't.

So if anyone has a good answer on how to define what NOT NULL really does I would be really happy. So far the only answer i have got was "Use NOT NULL on all fields, it is better"

Here is the SQL for my DB if anyone is interested

-- Basic DB Structure and construction.
-- Create database db_cixz
CREATE DATABASE db_cixz

-- Create member table in db.
CREATE TABLE cixz_members {
    -- Basic user information
    `member_id` int NOT NULL,            -- Unique member number, Primary key                    ; int, forces numeral value
    `status` BOOLEAN NOT NULL,            -- Sets the member status, active? true/false            ; BOOLEAN holds two values, either true or false
    `username` varchar(16) NOT NULL,     -- Membername, only used when logging on                 ; varchar(16), PHP Form forces user to use username length <= 16
    `password` varchar(32) NOT NULL,     -- Member password, used when logging on                 ; varchar(32), standard length of md5 hash
    `email` varchar(255) NOT NULL,         -- User E-Mail address 
    `salt` varchar(3) NOT NULL,            -- Salt, used for more secure password protection         ; varchar(3) since our salt is never longer than 3 chars
    `activation` varchar(32) NOT NULL,    -- activation, used when first activating the account    ; varchar(32), standard length of md5 hash
    `rights` tinyint NOT NULL,             -- User rights "0000" = basic rights, "1111" = admin    ; tinyint, allow a strict 4 digit number
    
    -- Detailed user information
    `firstname` varchar(90) NOT NULL,    -- Members first name eg John
    `lastname` varchar(90) NOT NULL,    -- Members surname eg Doe
    `phone_work` varchar(32) NOT NULL,  -- Members workphone, optional field
    `phone_cell` varchar(32) NOT NULL,  -- Members cellphone, optional field
    `phone_home` varchar(32) NOT NULL,  -- Members homephone, optional field
    `birthday` date NOT NULL,            -- Users birday
    `city` varchar(32) NOT NULL,        -- The city of residence
    `country` varchar(32) NOT NULL,        -- The country the member lives in
    `occupation` varchar(255) NOT NULL,    -- The occupation of the member
    `description` text NOT NULL,        -- Member description, personal story                    ; text since member might write alot of text    
    `timezone` tinyint NOT NULL,        -- Which time zone is used                                ; tinyint, valude from 1-12 depending on zone
    `joindate` timestamp NOT NULL,        -- The day the member join cixz                            ; TIMESTAMP used for format    '0000-00-00 00:00:00'
    `lastactive` timestamp NOT NULL,    -- Last time member was logged on                        ; TIMESTAMP used for format    '0000-00-00 00:00:00'
    
    -- User stats
    `logins` int NOT NULL,                -- Number of times user has logged on
    `ips` varchar(15) NOT NULL            -- Array of user IPs
    
    -- Basic forum information
    
    
    -- Detailed forum information
    
    
    PRIMARY KEY(member_id)
}

It isn't completely done yet but you get the idea.
Oh if you have any other input on the DB please feel free to share.

For question 1:
I would split up the phonenumbers. You will always output them seperately, never in that combined string, so you are just adding work for yourself. The way you are describing it you will call phoneNumbers from the database and then explode it into an array and then have a loop to run through the array and output it. Otherwise, all you need to do is go $row['cell'] etc.

Maybe some of the experts on this forum can give you stats or better reasoning to back this up, but thats definetly what I would do.

For question 2:
I can't give you a better idea on Null vs Not Null. But you don't really need it, just error check and you are fine.

Originally Posted by Truly

For question 1:
I would split up the phonenumbers. You will always output them seperately, never in that combined string, so you are just adding work for yourself. The way you are describing it you will call phoneNumbers from the database and then explode it into an array and then have a loop to run through the array and output it. Otherwise, all you need to do is go $row['cell'] etc.

Maybe some of the experts on this forum can give you stats or better reasoning to back this up, but thats definetly what I would do.

The reason I would do this instead of the several fields way is that I won't need to query the DB as many times which would take up BW and server resources.

On the other hand to explode the info will also take server resrouces. First I was going to do the several fields way since it is simpler to code, but if there are any benefits of going the other way I am open for it as well.

Originally Posted by Truly

For question 2:
I can't give you a better idea on Null vs Not Null. But you don't really need it, just error check and you are fine.

Well I really do want to understand it. I don't like to do stuff that I don't understand

For the 1st question, I'd separate the values in the db.
It's not faster for the db, and actually, a split on a string is slower than accessing different rows of an array.

For the 2nd, now...
First, you have to understand what a NULL value is.
It's nothing, it's unknown and undetermined. It don't means that there is nothing, because a null is not nothing.
I think that "unknown" is the best analogy for starters.
If you take a boolean field, it can have 3 states:
True, False and NULL. Null is different of true or false.
The same goes for a string (varchar) it can be "the fox runs away from the brown dog", or it could be "", or it could be null.
Now, the string "" is not null. Because you know what it holds; it's empty.
But a varchar NULL means that you don't know it content. It could be empty, but maybe not.

Now, for the "NOT NULL" keyword in your table, it simply means that you tells the db to put a constraint on your table field, saying that you refuse to have NULL values in that field.
You can have empty values (in the case of text, blob or varchar), but not null.
If you try to insert or update a row putting a NULL value, the db will abort the query.

I hope I could cast some light on that subject.
Don't hesitate to ask for more details, if you feel like.

Originally Posted by tripy

For the 1st question, I'd separate the values in the db.
It's not faster for the db, and actually, a split on a string is slower than accessing different rows of an array.

For the 2nd, now...
First, you have to understand what a NULL value is.
It's nothing, it's unknown and undetermined. It don't means that there is nothing, because a null is not nothing.
I think that "unknown" is the best analogy for starters.
If you take a boolean field, it can have 3 states:
True, False and NULL. Null is different of true or false.
The same goes for a string (varchar) it can be "the fox runs away from the brown dog", or it could be "", or it could be null.
Now, the string "" is not null. Because you know what it holds; it's empty.
But a varchar NULL means that you don't know it content. It could be empty, but maybe not.

Now, for the "NOT NULL" keyword in your table, it simply means that you tells the db to put a constraint on your table field, saying that you refuse to have NULL values in that field.
You can have empty values (in the case of text, blob or varchar), but not null.
If you try to insert or update a row putting a NULL value, the db will abort the query.

I hope I could cast some light on that subject.
Don't hesitate to ask for more details, if you feel like.

Hey and thx for the reply.

It is clearer for me now but it gave me two new questions .)
For what can I use a null value? If it is something unknown I don't see how we can use it for anything. And the other question... How do I insert something that is NULL?

I see that for most basic things NOT NULL is the better way to go since then NULL wont risk to mess up my DB Data.

What i mean is are there any uses for a null value?

What i mean is are there any uses for a null value?

There might..
Imagine a survey you would you user to take, a null value would mean that he did not took the survey, True that he took it, and False that he refused it.

It's clearly not the most used data type, but it can be of use

How do I insert something that is NULL?

simply!

insert into myTable (x, y) values ('there is a null somewhere', NULL)

in a table where the field y is nullable (not NOT NULL