Is This A Good Database Design - Very Long

Hey I was wondering if you
all could help me with my database. I am very noobish as databasing and I am
not sure what I have is good or bad. This is very long since I tell you basically
what my whole site does/will do. This is in hopes that it will give you a
better understanding of how many database will work.

 

Japanese WebSite

Purpose of the site:

 

This sites intention is to help people learn the 2 basic
Japanese character sets Hiragana and Katakana.
 
Hiragana is usually used for Japanese words and contains 46 basic characters,
plus a set of special characters. Katakana is usually used for foreign words
and also contains 46 basic characters plus a special set of characters.
Hiragana and Katakana are written differently when they are written with Japanese
characters. However if you right them in romaji (this is basically writing words
as if you where writing English and does not involve using characters. This is
used for foreigners while they are learning how to write the Japanese
characters).

 

Hiragana and Katakana are pronounced exactly the same.
This site will use a quiz style format. The user will first choose from the set
of Hiragana characters then the Katakana characters. Once chosen the website
will randomly choose a character and display the Japanese symbol of it.
  The user will then write the romaji
representation in the text box provided.

 


 It will be then checked
and if the user is right they will get a new symbol. If they are wrong they
will have try again.
  The purpose for
this is for the user to get use to see how the character looks like but at the
same time writing in romaji what will be easier for them. This should make it
easier for them the next time they see the character to know how to say it.

 

Version 1
 

 

The site will go through many versions. This version I am
focusing on using C#,asp and ms sql. Later on in future versions ajax and
javascrip will be introduced and parts will be changed. Version 1 also uses CSS
and html.

 

Version 1 will contain these features.


1.       A
practice section. A user will go through a wizard like setup that will first
let them choose from the basic 46 Hiragana characters (the other special
characters will be added later). They will then go to the next setup and choose
from the 46 basic Katanana characters. After they go to another section that
asks them how many repetitions they want to go through.
  If the user is a member(free membership) they
will have the option to save their settings that will be stored and later
available on the front page called quick links. The next page of the wizard is
actually going through the quiz. Once they are done they will see a summary of
how many they got right,wrong and how many they needed assistance on. At this
point they will be able to view a chart to see their progress(current chart
showing there last
  attempt, a week one
and a month one). These charts will be either made using c# or ms sql(I saw a
something that says you can make something like that but have not confirmed
it). I may do both ways for a learning experience.


2.       A
  Registration form, login and logout.


3.       Quick
link. Quick link will be for registered members so they don’t have to go
through the wizard every time to make their practice quiz up. A user will go
through the wizard once and choose what they want and save it.
  Quick link will be on all the pages in the
form of mostly likely a grid view. A user will be able to store 3-5 different quiz’s
(in the future maybe more). A user can then click on the quick link and it will
just take them to the quiz. The user will also will be able to update their
quiz through the grid view. This will take them to the start of the wizard and
they can go through it again and then just save over it. They will also be able
to delete there quick links through the grid view.

Currently done

Half of step 1 has been completed. Currently a user can
go through the wizard and select what they want and choose how many repetitions
and go through the quiz. However the summary and charts are not done.

 

Future versions.

 

Design will be a major part of it since I have not
focused on this at all. Also since I wanted to experiment with dynamic controls
I made all the checkboxes for the selections dynamically. This caused a problem
that a user must hit a save button since the controls have to get recreated and
updated.

 

I think normally this might not have been a problem but
since I since I am using a multiview(to create a effect that the wizard is all
on the same page and not the whole page has to be reloaded) it caused some
problems(I can’t really remember since it was a couple months ago when I
started but had to stop due to the amount of school work). This is why I want
to go back later and make them with ajax. This also makes another learning
experience.

 

Where I am at now.

 

When I started this I really did not think much of my
database mainly because I am not good at data basing and don’t really like it that
much. I have had a course in data basing (was with oracle though)that was part
of my program. At the time I did not understand very much since I found the
teacher going too fast and he started at chapter 8….. Also when you got 7 other
courses with it it’s hard to learn lots.

 

I have gone through some tutorials and stuff. I am not
very keen on reading a database book since I just don’t have the time since I
want to also read a book on c#, javascript, ajax and so forth. Also I don’t
know how much I will retain/learn from a book. With some of the stuff I just
find its better to try to something where you will be interested in and then
when needed read chapters of a book or ask questions.
  This is what I done for my c# stuff like I
had a c# with asp course and 2 months after the courses I started this site and
I had to ask myself what the heck did I learn in that course since it seemed I
knew nothing(and I was close to top in my class). With pounding it out and
reading I was able to accomplish what I needed. Like I still want to read a
book on C# but for now it’s just not going to happen same with Data basing.

 

All the pervious information was so you can understand
what my website is trying to do so you can better evaluate my database design
and answer my questions.

 

First Design of my
database.

 

This is when I just made it up on the spot and did not do
any database design or anything.

 

Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?

 

I have currently 2 tables How one called Hiragana and the
other Called Katakana

 

HiraganaID = PK

 

HiraganaID
  HiraganaCharacter HiraganaImage

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

1
           a
                 Images/hiragana/a-o/a.jpg

2
           i
                 Images/hiragana/a-o/i.jpg

3
           u
                 Images/hiragana/a-o/u.jpg

4
           e
                 Images/hiragana/a-o/e.jpg

5
           o
                 Images/hiragana/a-o/o.jpg

6
      
    ka
                Images/hiragana/ka-ko/ka.jpg

 

Katakana

 

KataKanaID = PK

 

KatakanaID
  KatakanaCharacter
                                  KatakanaImage

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

1
           chk_Kata_a
                                        
Images/katakana/a-o/a.jpg

2
           chk_Kata_i
                                        
Images/katakana/a-o/i.jpg

3
           chk_Kata_u
    
                                    Images/katakana/a-o/u.jpg

4
           chk_Kata_e
                                        
Images/katakana/a-o/e.jpg

5
          
chk_Kata_o
                                        
Images/katakana/a-o/o.jpg

 

I quickly found that this design was very bad since I was
unable to add new rows very easily. Each character set has each of its characters
in rows of 5. Some rows however have only 3 romaji characters so what they do
is leave the other cells blank representing that they don’t exist. I wanted to
do this too so I figured the easier way would be to have just null rows of
data. So say for “ya,yu,yo� it would be like this “ya,null,yu,null,o�. That’s
how it would appear in the database so when my c# would read it in it would not
have to account for rows that had less than 5 characters since it would have
already the nulls for it.

 

You can check a chart out here: http://www.alpha.ac.jp/japanese/img/moji.gif

 

So with this poor design it would result me in basically
rewriting the database.

 

I have thought of 2 possible ways to. I am not sure if
either way is good but I will try to explain my reasoning as much as I can.

 

Possible Way 1

 



 

In this approach I have 5 tables.

 

 

 

 

 

Hiragana

This table stores all the information about Hiragana
Characters

 

CREATE TABLE [dbo].[Hiragana](


      [HiraganaID] [int] IDENTITY(1,1) NOT NULL,


      [HiraganaCharacter] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [HiraganaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [SortOrder] [numeric](3, 0) NOT NULL,


 CONSTRAINT [PK_Hiragana] PRIMARY
KEY CLUSTERED

 

 

 

Katakana

This table stores all the information about Katakana
Characters

 

CREATE TABLE [dbo].[Katakana](


      [KatakanaID] [int] IDENTITY(1,1) NOT NULL,


      [KatakanaCharacter] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [KatakanaImage] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [SortOrder] [numeric](3, 0) NOT NULL,


 CONSTRAINT [PK_Katakana] PRIMARY
KEY CLUSTERED

 

 

Quick Links

This table will store the users quick link information.

 

CREATE TABLE [dbo].[QuickLinks](


      [QuickLinkName] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,


      [UserID] [int] NOT NULL,


      [SavedCharacters] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,


      [SavedImagePaths] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

 

Users

This will store login information.

CREATE TABLE [dbo].[Users](


      [UserID] [int] IDENTITY(1,1) NOT NULL,


      [UserName] [varchar](30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,


      [EmailAddress] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,


      [Password] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,


      [DateTimeStamp] [smalldatetime] NOT
NULL,


 CONSTRAINT [PK_Users] PRIMARY
KEY CLUSTERED

 

Charts

This will store all information that will be used for
charts.

 

CREATE TABLE [dbo].[Charts](


      [PracticeNum] [int] IDENTITY(1,1) NOT NULL,


      [UserID] [int] NOT NULL,


      [Correct] [numeric](3, 0) NOT NULL,


      [Wrong] [numeric](3, 0) NOT NULL,


      [AssitanceNeeded] [numeric](3, 0) NOT NULL,


      [TimeDateStamp] [smalldatetime] NOT
NULL,


 CONSTRAINT
[PK_Charts] PRIMARY KEY
CLUSTERED

(

 

 

Relationships

 

Users and QucikLinks have a PK to FK relationship this is
because quicklinks is dependent on the user having a user account. It also away
to identify which QuickLinks will belong to who.

 

Question 2:
  Currently I don’t have a primary Key for this
table what would be a good primary key for this table?

 

QuickLinks also does not have any relationship to either
to HiraganaCharacters or Katakana Characters. This is because I did not feel it
was necessary to grab the information from here. I simply could just grab it
from the array that holds this information from my c# code.

 

Question 3:
  How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName).
  Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?

 

Option 1

 

QuickLinkName
     UserID
     
SavedCharacters
  
 SavedImagePaths
                                                                                                                                                                              

   

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

QuickLink1
         1
     
              a
     
     image/a.jpg

QuickLink1
         1
     
              i
     
     image/i.jpg

QuickLink1
         1
     
              u
     
     image/u.jpg

QuickLink1
         1
     
              e
     
     image/e.jpg

QuickLink1
         1
     
              o
     
     image/o.jpg

QuickLink1
         2
     
              a
     
     image/a.jpg

QuickLink1
         2
     
              ya
    
     image/ya.jpg

QuickLink1
         2
     
              ki
    
     image/ki.jpg

QuickLink1
         2
     
              yo
    
     image/yo.jpg

QuickLink1
         2
     
              n
     
     image/n.jpg

























 




Option 2

 


             
 

QuickLinkName
     UserID
     
SavedCharacters
  
 SavedImagePaths
                                                                                                                                                                              

   

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

QuickLink1
         1
     
              a,i,u,e,o
     image/a.jpg, image/i.jpg,


                                        
      image/u.jpg, image/e.jpg,


                                                image/o.jpg,


                    
      

QuickLink2
   
   
 2
             
   a,ya,ki,yo,n
      image/a.jpg,
image/ya.jpg,


                                        
      image/ki.jpg, image/yo.jpg,


                                                image/n.jpg,


             

Charts table also has a Relationship with Users

 

The thinking for this one is that Charts will use the
UserID to tell who this data belongs too. It will store the number of correct
answers, wrong answers and AssitanceNeed. This table will also hold a timeDateStamp.
The reason for this is because I want to have a chart that displays the last
Quiz they did and also the stats of a week’s worth of quizzes, and month’s
worth of quiz’s. With a timeDateStamp I should be able to add up all those columns
and then display them for whatever period I choose.

 

Second Possible
Way.

  

Characters

 

CREATE TABLE [dbo].[Chracters](


      [CharacterID] [int] NOT NULL,


      [CharacterName] [varchar](5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [CharacterPath] [varchar](50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,


      [SortOrder] [numeric](3, 0) NOT NULL,


 CONSTRAINT [PK_Chracters] PRIMARY
KEY CLUSTERED

 

This way uses 4 charts instead of 5. The main difference
is that the Hiragana Charts and Katakana Charts have been merged into one
table.

 

This way has another relationship between Characters and
Quick Links.
  This will store the Character
ID instead of the actual Characters and Path through the use of C# code. When a
user selects a box it will make a note of the CharacterID. When it is time for
the user to use the QuickLink it will join the tables together and filter out
the CharacterIDs. Giving me the CharacterName and Path.

 

The problem with this way is that well the code I written
will need to be changed since currently it grabs stuff from 2 tables into one.
I don’t know if this would save anytime over the first way either.

 

I hope these are sort of close to an alright design since
I did try to think about how my website would interact with the database.

 

Sorry for it being so long.

 

Summary of Questions

 

Question 1: I
forgot how to limit select query results. Like If I just want to show 10 query
results how do I do this?

Question 2:
  Currently I don’t have a primary Key for this
table what would be a good primary key for this table?

Question 3:
  How can I make this table for all users? Like
Say they choose 5 characters and save it as QuickLink1(as the
QuickLinkName).
  Should each of these 5
characters gets its own line or should it all be saved in one row? Or should I
even have another table to hold this data?