questions

[groundup]

Why does deletedby have a -1 in instant_messages?

why do we use the unix timestamp to store stuff in mysql when mysql is easier to read, a smaller number, and just as useful?

should "Guest" have a place in log_boards, log_topics, etc?

[Joseph Fung]

Ok - re. the deletedBy, if I remember right -1 means it hasn't been deleted by anyone.  0 means it was deleted by the sender and 1 means it was deleted by the recipient.

The reason for this is that it means any given IM message only needs to be stored once, and we can use that flag to decide when it should be shown or removed from the DB.

We are using the unix time stamp because PHP's time functions use the unix time stamp. If we use the MySQL datte and tiem field it would have to do a conversion every time we wanted to do something with the timestamp.  Additionally, using a unix timestamp means were not restricted to a mysql database.  it leaves us open for future development.

[groundup]

thank you, why didn't you just use 2 instead of -1? it is 1 less byte and you can use unsigned attribute?

[Joseph Fung]

Excuse me? No - it doesn't use one less byte.

If it were stored in a text field then yes - you would be right.  But currently (I'm pretty sure - I could be wrong) it's using an integer field.

Also, it takes the same number of bits to store a 2 as it does to store a -1.

In Binary, a two is 10 while a -1 is stored as 11 (note, this is assuming the machine is designed to store it in 2's complement form - which almost all are)

So, the only time using 2 instead of -1 would yeild any benefit is if we were using text fields, which wouild render "unsigned" useless.  And it would be pointless to do that as we'd be using a full 8bits to store each number - which is what is happening now anyway.

If you want, I can give you a more detailed explanation if I did that too quickly.

[groundup]

I didn't check but I thought I saw it using something other than tinyint(1), which would be boolean. Wouldn't tinyint(1) unsigned mean from 0-9? does the 1 signify a digit/place/character?

[Joseph Fung]

I didn't check but I thought I saw it using something other than tinyint(1), which would be boolean. Wouldn't tinyint(1) unsigned mean from 0-9? does the 1 signify a digit/place/character?

tinyint(1) would have one of two meanings.

The first is how many bits.  If this is what it means, tinyint(1) would, indeed represent 1 bit - which would be boolean, and therefore useless in this circumstance (meaning two values is insufficient - we need 3 values for this system).  I'm fairly certain, however, that in mysql, tinyint is always restricted to 1 byte lengths - which is 8 bits.

Likely the 1 refers to how many bytes - specifically with respect to how many ascii characters it can contain.

I think you are misunderstanding the way the data is stored.  If it's storing the ascii value of a number, it's actually storing 0x30   <digit> for each character (i.e. 36 would be stored as 0x33 0x36 taking up two bytes).  If it's storing the integral value, it stores the value.  So 36 would be stored as 0x24 taking up one byte.

Essentially, if the field is a tinyint, it's as efficient as it's going to get.

[Greg Robson]

The 1 in tinyint(1) refers to the maximum number of characters to display. It has no effect on how the data is stored.

FYI
tinyint, 1 byte, -127 - 128 or 0 - 255
smallint, 2 bytes, -32768 - 32767 or 0 - 65535
mediumint, 3 bytes, -8388608 - 8388607 or 0 - 16777215
int, 4 bytes, -2³¹ ... 2³¹-1 or 0 ...2³²-1
bigint, 8 bytes, -2⁶³ ... 2³¹-1 or 0 ...2⁶⁴-1