• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PKs in two tables

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have 2 tables in a Socialnetwork schema.
[1] registredUser- userId, userName, Password, age, sex,city- userId is PK and Auto Increment
[2] userinfo- userId, userName Password- userId is Pkey and Auto Incremnt

When a user login on a login.jsp, he submits username and Pasword.

These two parameters of login.jsp are checked in registeredUser Table. If matched, he could log in.

If he is unable to log in he has to register in registration.jsp supplying username, password, Age, sex and city parametsrs. These records are added to the RegistredUser table.

How can I add username and password in the userInfo table when the user has successful log in so that next time the check has to be in userinfo table only, not in registred table?


 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why do you have two tables containing the same information? What is the "userInfo" table for? This data all relates to a single logical entity - a specific user - so it should normally be stored in the same record in the same table. You can always use SQL views with different permissions to restrict visibility of certain columns, such as password. Relational data models are designed to store each data value just once, except for foreign keys, so there is no need to duplicate information like this (de-normalisation is a different issue).

If you have to have two tables containing information relating to the same logical entity e.g. to keep the password in a completely separate table, then this is effectively a 1:1 relationship, so both table records should have the same PK value for a given logical record (user). You would therefore create the PK in the first table A, e.g. using auto-increment, then use that value as the PK for the other table B. Create a foreign key from B back to A to enforce the relationship. Don't create different PKs for the same logical record in the two tables.
 
induz sat
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the clarifications about 2 tables and PKs.
userId in Table 1[registeredUser] is the PK, that would be the same in the userInfo table. I could have stored all the records in a table[ registredUser] but for my learning I want to have 2 tables.

Now the big Q is when the user is being registered through Registration and UserRegistration servlet, how could I keep the PK FK relationship among both tables.

Column name, userId, is the same in both the Tables. userId in registredUser is PK; but I am not sure about its FK realtionship in userInfo

Hope to get some heads-up.

 
induz sat
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What should be the query for checking userName and Password from registredUser table to be valid and matching with the Parameters[ 'user' and 'pw' ] from Login.jsp.
I am getting error and query is not working...please help

 
reply
    Bookmark Topic Watch Topic
  • New Topic