9 Replies - 1557 Views - Last Post: 11 February 2012 - 02:25 PM Rate Topic: -----

#1 sport10  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 21-September 10

Database Design Input

Posted 11 February 2012 - 12:01 PM

I am having an issue with the design of a investment database that I am using MySQL and java to implement. I want my program to have three account a system admin, a investment manager, and a customer. I want the customer to be able to login and just be able to check the balance of any account he/she may have. I want the investment manager to be able to look at his customers account in order to make suggestions, and finally I want the system admin to be able to edit all the information of all the users. For example for a 401k account the admin can make changes to the customers income or rate of interest. I am having a tough time deciding on the tables. I want to have a customer table with all the personal information of the customer, and then an employment table the information in here will be the employment information of a customer. I also want to make a login table but am not sure how I can do this, is there anyway I can take the first and last name of the information in the customer table and make it the username, then take the ssn and make it the password? Anyone have any ideas on table design (what tables to use) and how to create the login table?
Thanks

This post has been edited by sport10: 11 February 2012 - 12:16 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Database Design Input

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Database Design Input

Posted 11 February 2012 - 12:27 PM

Taking a person's Names and SSN as username and password is not good practice at all! They are easily discover-able. Why don't you make the customer register with a unique username and password first?
a simple Login table's attributes would be,

UserID
UserName
Password


You'll have to have the UserID attribute in both Customers table as well as Login table 'cause you must link those 2 tables to identify customers individually.

This post has been edited by nK0de: 11 February 2012 - 12:30 PM

Was This Post Helpful? 2
  • +
  • -

#3 sport10  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 21-September 10

Re: Database Design Input

Posted 11 February 2012 - 12:40 PM

Thanks for that. I will do that then and make a register button that will prompt for a username and password creation. Any ideas on the table layout for the design? I just need some advice on how to make the layout for the tables.
Was This Post Helpful? 0
  • +
  • -

#4 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Database Design Input

Posted 11 February 2012 - 12:49 PM

Actually I have already showed you that too.

Quote

a simple Login table's attributes would be,

UserID
UserName
Password


You just have to set the suitable Data types for those.

For a complete guide to Relational Database Designing, there a great tutorial by Atli right here on DIC. Check it out.

This post has been edited by nK0de: 11 February 2012 - 12:51 PM

Was This Post Helpful? 0
  • +
  • -

#5 sport10  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 21-September 10

Re: Database Design Input

Posted 11 February 2012 - 12:58 PM

I mean for the project in general. I know Ill need a customer table, a employer table, and a login table. I was just looking for ideas on additional tables that others may see to fit. If it helps I plan on having 3 accounts that customers can enroll in a retirement account (401k), a medical plan, and a education plan.
Was This Post Helpful? 0
  • +
  • -

#6 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1156
  • View blog
  • Posts: 2,538
  • Joined: 05-May 05

Re: Database Design Input

Posted 11 February 2012 - 01:18 PM

Each account is it's own entity. So they'll need to be tables. Each account record will have a foreign key pointing to the the primary key of the customer table.

The customer table could have a foreign key to the employer, but then you limit the number of possible employers. It would be better to create a linking table.

I don't see a problem with creating user name and password fields in the customer table.
Was This Post Helpful? 1
  • +
  • -

#7 sport10  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 21-September 10

Re: Database Design Input

Posted 11 February 2012 - 01:21 PM

Any idea on how to manage the three account types that I mentioned in the initial post?
Was This Post Helpful? 0
  • +
  • -

#8 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1156
  • View blog
  • Posts: 2,538
  • Joined: 05-May 05

Re: Database Design Input

Posted 11 February 2012 - 01:30 PM

Quote

Any idea on how to manage the three account types that I mentioned in the initial post?


Can you be more specific? Manage can mean many things. Whose managing? Are you talking about the users managing accounts from the application? So, how would an application change database information?
Was This Post Helpful? 0
  • +
  • -

#9 sport10  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 21-September 10

Re: Database Design Input

Posted 11 February 2012 - 01:34 PM

I mean how would I implement the three possible accounts. Do I just add a record in a user table and call it account_type and put in what I want the account type to be?
Was This Post Helpful? 0
  • +
  • -

#10 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: Database Design Input

Posted 11 February 2012 - 02:25 PM

The 3 accounts being retirement account, medical plan, education plan, am I right?
If you have multiple entites for each account, I suggest you make a separate table for Account types too.

Quote

add a record in a user table and call it account_type


You mean a field. Not a record. If a user can enroll in multiple accounts, adding just one account_type field to the user table wouldn't do, I think.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1