wood burning stoves 2.0*
The moose likes JDBC and the fly likes Working with multiple database Tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Working with multiple database Tables " Watch "Working with multiple database Tables " New topic
Author

Working with multiple database Tables

Shashank Anand
Greenhorn

Joined: Oct 30, 2008
Posts: 26
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

Joined: Sep 30, 2008
Posts: 198
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?


My Blog
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30146
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Shashank Anand
Greenhorn

Joined: Oct 30, 2008
Posts: 26
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

Joined: Oct 13, 2005
Posts: 38107
    
  22
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30146
    
150

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

Joined: Sep 30, 2008
Posts: 198
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30146
    
150

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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Oct 13, 2005
Posts: 38107
    
  22
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

Joined: Oct 30, 2008
Posts: 26
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

Joined: Oct 06, 2007
Posts: 338
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Working with multiple database Tables
 
Similar Threads
Which comes first - Object Modelling or Data Modelling
Netbean project demo
Where to cache common parameters for Web Services
Transaction for Palm conduit?
Mysql installation