Help with Database

[SinNisTeR]

Ok. Im new to databases and I have a few questions. What would be the best way to implement a user account setup? Basically my site will have users who can log in and keep computer parts in their personal inventory.

should i create a TABLE called users and store:

name
email
login
password
parts - how would i store parts?

how would user logins work?

how would i store parts in the same way newegg stores them? im kind of lost as to the data structure and its implementation. any good tutorials? ive looked around and there's nothing specific to what im interested in.

This will be all done locally, im just messing around with postgresql and jsp.

 

[SinNisTeR]

?

 

[Mucman]

Your question is difficult to answer becuase that's the subject that people have studied for years attempting to figure out. What you are looking for is "relation database design". I can think of 2 entities and 1 relationship in your design. Your entities are Users and Parts. The relationship is ownership of parts.

Very good intro article

 

[znaps]

Originally posted by: SinNisTeR
Ok. Im new to databases and I have a few questions. What would be the best way to implement a user account setup? Basically my site will have users who can log in and keep computer parts in their personal inventory.

should i create a TABLE called users and store:

name
email
login
password
parts - how would i store parts?

how would user logins work?

how would i store parts in the same way newegg stores them? im kind of lost as to the data structure and its implementation. any good tutorials? ive looked around and there's nothing specific to what im interested in.

This will be all done locally, im just messing around with postgresql and jsp.

There's many ways to do it, but you might have your DB tables setup something like:

USERS
--------
login_id
email
name

SECURITY
------------
login_id
password

USERS_PARTS
------------
login_id
part_id

PARTS
________
part_id
part_description


Basically, if this looks totally foreign to you, then you'll need to take a course on relational databases.

 

[WildHorse]

In your opening post, you ask, "should i create a TABLE...?"

No. You'd probably have at least 4 tables:

users(userNum, lastName, firstName, email, pwd)

part(partNum, desc, qtyInStock, cost)

order(orderNum, orderDate, userNum)
..........................................................fk

orderLine(orderNum, partNum, qtyOrdered)
......................fk................fk

and probably some other reference tables.

Primary keys underlined. Foreign keys underneath.

Recommend you get a book, or far better to take a course, and learn about database normalization, which encompasses the subjects of setting up tables & relationships. Getting on top of that takes awhile.

When you use something like Word or Excel or Firefox, things are intuitive so you can accomplish something by diving in & clicking around.

Database is a different world from that.

You'd almost certaintly want to set everything up so it'd be expandable.

Also, you maybe helped by this.

Good luck to you.

 

[SinNisTeR]

I know a little about relational databases. From what i've read, i have no concerns, all makes sense. I've also heard about using a database to store objects. Since my site is done in jsp, it would make sense to store user objects and part objects into the database, no? I've heard good things about Hibernate, which allows me to do that. Is this a bad approach?

 

[WildHorse]

Originally posted by: SinNisTeR
I've heard good things about Hibernate, which allows me to do that. Is this a bad approach?

Unfamiliar with it, so can't comment.

 

[znaps]

Originally posted by: SinNisTeR
I know a little about relational databases. From what i've read, i have no concerns, all makes sense. I've also heard about using a database to store objects. Since my site is done in jsp, it would make sense to store user objects and part objects into the database, no? I've heard good things about Hibernate, which allows me to do that. Is this a bad approach?

Hibernate actually just maps your Java objects onto a relational DB, generating the SQL for you transparently.

I wouldn't advise you, as a beginner to SQL, to use Hibernate - use JDBC until you're proficient with the intricacies of databases, referential integrity, transactions, isolation levels....