Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Working with multiple database Tables

 
Shashank Anand
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everybody,

I am developing an application that has two tables, users & books.
A user requests for a book and the requests are authorised by the administrator. I need suggestions on how to do request / authorization using sql database tables so that I can start working on it.

I really don't know how to start with this so please advise me.

Regards,
Shashank.
 
Vinod K Singh
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe you are trying to build a multi user / multi step application where users requests for books using some online form and later administrator authorizes using some other form.

If this is true then what is the problem?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shashank,
The first step is to create the schema for tables. Have you done that yet? If so, what are you up to? If not, what do you think would be good to go in both tables.
 
Shashank Anand
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am a beginner in java and I am doing this with single connection.

I am not creating schema for this. I have a vague idea like when the user selects a book, status of a column (requested) changes for that book (boolean value) and the book name and user name are sent to admin so he can select from a list for authorizing. After authorizing, the status of another column (authorized) changes. So at any time I can query how many books are in requested status and how many are authorized. It is possible to do it this way ?

I have created a table that stores name and password information of admin and other users, so when they log in, they get different screens like, request screen for user and authorize screen for admin.

Regards,
Shashank.

[ November 15, 2008: Message edited by: Shashank Anand ]
[ November 15, 2008: Message edited by: Shashank Anand ]
 
Campbell Ritchie
Sheriff
Posts: 48921
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to be very specific about what goes into your tables and their structure. If you have vague ideas now, you will get vague errors later on . . .
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shashank,
That's fine. You don't need a separate schema. You do need table definitions. It sounds like you have one - the user table. What about the books table?
 
Vinod K Singh
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Shashank Anand:
I have a vague idea like when the user selects a book, status of a column (requested) changes for that book (boolean value) and the book name and user name are sent to admin so he can select from a list for authorizing. After authorizing, the status of another column (authorized) changes. So at any time I can query how many books are in requested status and how many are authorized. It is possible to do it this way ?
Book requests should be stored in a separate table so that scenarios where many users request same book can be handled. There might be multiple copies of one book, so multiple user requests could be fulfilled.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what you are asking here. You still haven't given any details about the tables (like what field they join on) or what your specific problem is (are you having trouble writing the query, the JDBC code, something else.) Please provide some more detail about what you want people to tell you.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i would suggest working exclusively with the tables in SQL until you get them modeled the way you think works for you... the app should come later.

For each of your tables... first ignore the other table... (i.e., exclusively look at Users)... identify the data that describes a user (id, name, etc). Do the same with Books (id, title, quantity, etc). Identify foreign key relationships that need to exist between Users and Books. Identify where Indexes may improve performance with searches. Identify constraints that should exist within and between tables.

I would not put my user passwords in the common user table... they should be in a separate table (id, password) and probably in a separate schema accessible only by the application... but... we're not thinking about the application right now... just your data.
 
Campbell Ritchie
Sheriff
Posts: 48921
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Campbell:
i would suggest working exclusively with the tables in SQL until you get them modeled the way you think works for you...
When you have it working, then you write down the details of all the tables, and that is a schema.
 
Shashank Anand
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
Thanks to you all for your advices. Taking your suggestions I created tables and schema for them. I got an idea of how to work with multiple tables. My application is working fine but for three problems. Please help me with this.

1) I have 2 columns of type DATETIME : date1 and date2. I inserted current date into date1 using preparedStatement as below:
java.util.Date dt = new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String datetime = sdf.format(dt);
I want date2 =(date1 + 15days). How can I add 15 days to date1 ?

2) I have 2 columns firstname and lastname which take their value from textfields using preparedStatement. The table may take same firstname and lastname individually any number of times, but I want the combination to be unique. How to achieve that ?

3) I am using mysql server and I have stored user & password information in mysql.user table using: CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1'; I am able to retrieve the data and privileges of users.
I have also created a table named access table and stored the same username and password in it.
I have designed a login page that lets the user to enter username & password and compares with access table. On logging in the user gets a different frame. From which table (access table or mysql.user table) and how can I fetch the username of the person who has currently logged in so that I can display his username on the screen?

Thanks
Shashank
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Shashank Anand:
Hi All,
Thanks to you all for your advices. Taking your suggestions I created tables and schema for them. I got an idea of how to work with multiple tables. My application is working fine but for three problems. Please help me with this.

1) I have 2 columns of type DATETIME : date1 and date2. I inserted current date into date1 using preparedStatement as below:
java.util.Date dt = new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String datetime = sdf.format(dt);
I want date2 =(date1 + 15days). How can I add 15 days to date1 ?


If it is always going to be current + 15, use a calendar instance and add 15 days create the date2 and insert it at the same time... or

Create an after insert trigger that adds the days when you insert date1 (make it an after update trigger if date1 can be added after inserting the row.).

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_adddate




2) I have 2 columns firstname and lastname which take their value from textfields using preparedStatement. The table may take same firstname and lastname individually any number of times, but I want the combination to be unique. How to achieve that ?


create a unique index that uses the two columns as a compound key


3) I am using mysql server and I have stored user & password information in mysql.user table using: CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1'; I am able to retrieve the data and privileges of users.
I have also created a table named access table and stored the same username and password in it.
I have designed a login page that lets the user to enter username & password and compares with access table. On logging in the user gets a different frame. From which table (access table or mysql.user table) and how can I fetch the username of the person who has currently logged in so that I can display his username on the screen?

Thanks
Shashank


This table is the one you're looking for: processlist
http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic