[SQL] Database structure

[Guest]

I?m in the process of re-designing a database at the moment. I?ve spent the last few days on it to make sure that it?s perfect. I?m aiming to have it in 3NF with cascading updates and deletes.

Guess I have a few questions:

• Most of my tables have primary keys; others are just relationships such as ?enrolments? ? what students are assigned to classes. At the moment this table has two regular keys ?class_id? and ?student_id?? should both of these be primary keys (or unique) or should I just leave the table the way it is?
  
• I have not specified any limits for my varchars. Is this bad practice or does it even matter seen that they are ?variable length? characters?
  From what I understand one byte equates to one regular latin character. This will be a multi-lingual application so the tables will need to be UTF-8 character encoded. How does this affect the varchars length?
  Eg: varchar(25) how many UTF-8 characters can this store, will this allow the same amount of characters no matter what language script is used?
  

Thanks in adva:pe ? hope someone can help :p

[Guest]

Just found on oracles website that:

"One Unicode character can be 1 byte, 2 bytes, 3 bytes, or 4 bytes in UTF-8 encoding. Characters from the European scripts are represented in either 1 or 2 bytes. Characters from most Asian scripts are represented in 3 bytes. Supplementary characters are represented in 4 bytes."

So... does this mean that if i want a varchar field to store a maximim of 10 characters - i would have to define the field as varchar(40)?

I'm guessing i'll have to check programmatically whether the user the user has entered more than 10 characters as this would also allow 40 regular ASCII characters.

Correct?

[+theblazingangel MVC]

no, you'd still set it as varchar(10). In the background each character simply takes up between 1-4 bytes for oracle to store on the hdd/ram, rather than just 1. The limit on the number of characters will be 10.

If you've got a link table, like students >- link -< classes, containing student_id and class_id, both fields will need to be individually set as foreign keys, and the two combined will be set as the primary key for that table.

[Guest]

no, you'd still set it as varchar(10). In the background each character simply takes up between 1-4 bytes for oracle to store on the hdd/ram, rather than just 1. The limit on the number of characters will be 10.

So does this apply to MySQL also or is this just a feature unique to oracle databases?

Just looked up MSDN's definition of varchar: "Variable-length non-Unicode character data".

It seems to suggest that I should use nvarchar for storing Unicode character data. I've never heard of nvarchar... does it really matter?

[edit]

Seems that i should be using nvarchar afterall

"The difference between varchar(300) and nvarchar(300) is that the maximum varchar size is 300 single-byte symbols. The definition nvarchar(300) means that it will store up to 300 symbols multibyte encoded, regardless of their code length."

http://social.msdn.microsoft.com/Forums/en...84-d1a4daba4ef2

[/edit]

If you've got a link table, like students >- link -< classes, containing student_id and class_id, both fields will need to be individually set as foreign keys, and the two combined will be set as the primary key for that table.

So... "class_id" and "student_id" are foreign keys of their respective tables and both primary keys of the link table?

Im guessing the same applies for other tables such as a high score table:

student_id - foreign and primary key

game_id - foreign and primary key

score - primary key

date_earned - primary key

btw... thanks for the help, really wanted to build a solid database before starting into the code :p

Edited November 11, 2009 by Guest

[Guest]

Looking at the MySQL reference manual it says that:

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions

assuming i'm using a version older than 5.0.3 i can only store 255 ASCII / UTF-8 characters - right?

[+theblazingangel MVC]

If you're using MySQL < v5.0.3 and you want to store more than 255 characters (ASCII/UTF-8, no difference!), you'll need to use a blob/text type column.

MySQL does not have nchar/nvarchar type fields, it only has char and varchar. To support unicode it instead allows you to specify character sets and collations. Otherwise, with other dbms's yes, you'd use nchar/nvarchar.

http://www.dotnetspider.com/forum/176341-I...type-MySql.aspx

[Guest]

Yea, ran into that problem today... MySQL kept converting my nvarchars into varchars

CREATE TABLE IF NOT EXISTS `classes` (
`class_ID` INT NOT NULL AUTO_INCREMENT ,
`teacher_ID` INT NOT NULL ,
`class_name` VARCHAR(50) NOT NULL ,
`enrolment_key` VARCHAR(50) NOT NULL ,
`enrolment_lock` VARCHAR(10) NOT NULL DEFAULT 'closed' ,
PRIMARY KEY (`class_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Is this enough then to support unicode then?

[+theblazingangel MVC]

afaik, yes i believe so!

So... "class_id" and "student_id" are foreign keys of their respective tables and both primary keys of the link table?

Im guessing the same applies for other tables such as a high score table:

student_id - foreign and primary key

game_id - foreign and primary key

score - primary key

date_earned - primary key

Do score and date_earned really need to be part of the primary key for this table? Can students actually earn more than one score per game_id?

[Guest]

afaik, yes i believe so!

Good, i can finally get on to some code now :)

Do score and date_earned really need to be part of the primary key for this table? Can students actually earn more than one score per game_id?

Thats be the high score table... it stores the highest score a person got in the table.

If the person beats the game for the first time a record is stored in that table. If they beat that score the next time they play, the record is modified to show the highest score. In this case im guessing all the fields need to be set as the primary key.

[+theblazingangel MVC]

Thats be the high score table... it stores the highest score a person got in the table.

If the person beats the game for the first time a record is stored in that table. If they beat that score the next time they play, the record is modified to show the highest score. In this case im guessing all the fields need to be set as the primary key.

If you're replacing score and date_earned whenever a higher score is achieved then no, student_id + game_id will be the primary key. This will allow one and only one record per student per game! Adding further columns to the primary key breaks this restriction, and your database will not be fully normalized!

I question the need for this table though. I presume you have another table where you store all of the scores achieved, why not simply query this to get the information you desire rather than duplicating data. If you want to get the high score data from the score table like you currently have in the high score table, sort you records by score, then date, and then group by student_id and game_id (e.g. SELECT * FROM scores ORDER BY score, date_earned GROUP BY student_id, game_id; not sure if this is correct sql, been a little while...).

btw, in reality normalization in database design is often slightly compromised in exchange for simplicity/efficiency/performance/whatever, so don't worry too much if your design isn't fully 3NF!

[Guest]

Gah... your right, wasn't thinking about that. game_id and student_id are the only primary keys needed.

This is the only table that scores high scores. Each game will add the highest score a player achieves into this table. No point recording all the scores a player achieves if they are only ever interested in their highest score.

Thanks again.

[Guest]

Just one more question...

at the moment i have a student table, this contains their student_id which is a primary key along with their username, password and profile information.

would it be more efficient to split this table into two, one just storing the unique id, username and password while the other table storing their profile info such as real name, dob, bio etc...

student_table

------------------------

student_id

username

password

profile_table

------------------------

forename

surname

date_of_birth

nationality

bio

interests

etc...

I'm just trying to plan in advance... just incase one day this table has millions of entries.

Is this necessary or does the primary key index remove the need for this?

Thanks

[+theblazingangel MVC]

I'm really not sure on that one sorry, I don't actually have a lot of experience behind me.