aspose file tools*
The moose likes JDBC and the fly likes help needed on table structure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "help needed on table structure" Watch "help needed on table structure" New topic
Author

help needed on table structure

Souvvik Basu
Ranch Hand

Joined: Apr 05, 2010
Posts: 96
Hello Friends,

I have a requirement to create an online exam website for the client. I am just beginning with the development, and I'm confused about how to structure my tables. I'm using Struts 1.3 framework.

One of the basic objects in the application is a User (a java object). The User object has several variables, like name, address, phone no., userId etc. At the other end of the line is a Question object, which represents a 'multiple-choice question' with its correct option, and a questionId. Now, according to my design, multiple questions make up a QuestionPaper object, multiple QuestionPaper objects make up a Test object, and each User object has an array of Test objects (representing those tests that the user is allowed to take).

So, the structure is :
User has array of Test objects...each of those Test objects has an array of QuestionPaper objects, and each of the QuestionPaper objects has an array of Question objects.


Can anyone please help me with how to design the database tables? For example, how will I represent the array of Test objects in the table meant to hold user data.

chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1873
    
  16

Souvvik Basu wrote:I have a requirement to create an online exam website for the client. I am just beginning with the development, and I'm confused about how to structure my tables.

So basically you are expecting to be paid for taking on a job you don't understand and are not qualified to perform. Let's hope your client doesn't read JavaRanch, eh?
Souvvik Basu wrote:One of the basic objects in the application is a User (a java object). The User object has several variables, like name, address, phone no., userId etc. At the other end of the line is a Question object, which represents a 'multiple-choice question' with its correct option, and a questionId. Now, according to my design, multiple questions make up a QuestionPaper object, multiple QuestionPaper objects make up a Test object, and each User object has an array of Test objects (representing those tests that the user is allowed to take).
So, the structure is :
User has array of Test objects...each of those Test objects has an array of QuestionPaper objects, and each of the QuestionPaper objects has an array of Question objects.

Can anyone please help me with how to design the database tables? For example, how will I represent the array of Test objects in the table meant to hold user data.

Not sure how much you know about data modelling, and this is not the place to go into Relational Data Modelling 101 (you could try reviewing the free Coursera Databases course). You can even buy books full of standard data models for particular industries/applications. If you want to develop database applications, why not make an effort to learn a bit about data modelling first?

Anyway, just for clarity, your tables represent entities e.g. Users or Students etc. Each table must have a primary key, which is an attribute (not "variable") or combination of attributes that uniquely identifies one row in that table. If you don't already have one, it's often a good idea to define a numeric ID (which can often be generated by the database) as your PK, but you still need to know the "real" (business) key e.g. student name + date of birth, so that you don't do stupid things like adding the same record twice because you don't understand what makes it the same as (or different from) other records in the table. You can work out your basic table structures - keys and attributes - by going through the data attributes and working out which ones are uniquely identified by a given key. The basic rule of thumb is that the non-key attributes should be dependent on the key, the whole key and nothing but the key.

There are no "arrays" inside a given record in a typical relational table (this can be done on some databases but it's not a good approach here). If you think you need to have an array in your table, you don't. You really need two (or more) tables with foreign keys between them. Google "relational data modelling" or "third normal form" for more detail.

The one-to-many relationships between your tables are implemented via foreign keys. If one Test has many QuestionPapers, then you need a QuestionPaper table and a Test table, and a foreign key between them to describe the "has many" relationship. Again, do some research of your own to figure this out.

As for your original question, Google "many-to-many" relationships, which are implemented via an "intersection entity", as it sounds like you have one of these hidden in the relationship between Students and Tests e.g. how many Tests can a Student take, and how many Students can take a given Test? This thread may help you.

There are far more things you need to think about for a real-world application e.g. performance, security, etc. But right now it sounds like you don't even have a clue about the basics, so there's not much point going into that stuff at this point.

Good luck


No more Blub for me, thank you, Vicar.
Souvvik Basu
Ranch Hand

Joined: Apr 05, 2010
Posts: 96

chris webster wrote
So basically you are expecting to be paid for taking on a job you don't understand and are not qualified to perform.


Well...no. I'm not being paid for this. To be honest, I've taken this up more as a means to learn the things rather than provide a client with a deliverable. Its just that...having real requirements gives me that extra excitement. I'm more than happy that someone has given me a requirement, and that someone is more than happy to have someone do it for free for him.


chris webster wrote
But right now it sounds like you don't even have a clue about the basics


Thats correct. I'm more of a java developer, and I've always had the tables and other database stuff done by someone else, and I've written the java part of the code. This is the first time I'm trying to do something completely myself.

Thanks a lot for your help. I did have a very faint idea on how to proceed; but your post gave me a clear direction. Thanks again for taking the time to help me out Chris.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: help needed on table structure