[sport10]

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

[nK0de]

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

[sport10]

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.

[nK0de]

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

[sport10]

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.

[blackcompe]

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.

[sport10]

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

[blackcompe]

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?

[sport10]

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?

[nK0de]

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.