×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DB schema help

DB schema help

DB schema help

(OP)
Hello all.

I am creating a small backend DB for an application. The tables required number only a few, but I have some doubts as to the best way to set it up. I want to record a User ID and PW, along with journal entries, appointment information, and financial deadlines. My initial set up looks like this:

tblUsers
uID (pk) (text)
uPW (text)

tblJournal
jID (pk) (code generated unique number prefixed with "J")
jSubj (text)
jText ("memo" type field - long text)
jDate (date/time)

tblAppointments
aID(pk) (code generated unique number prefixed with "A")
aSubj (text)
aText ("memo" type field - long text)
aDate (date/time)
aStartTime (date/time)
aDuration (double)

tblFinancial
fID (pk) (code generated unique number prefixed with "F")
fSubj (text)
fText ("memo" type field - long text)
fDateDue (date/time)
fAmount (currency)

tblUser:Entry
uID (pk composite) (fk - tblUsers)
entryID (pk composite) (fk - table to which the entry belongs, as determined by the letter prefix)

This design doesn't seem like it is the most normalized or well designed to me. I'm a novice to DB design, so I would like some input as to how to better organize / normalize the tables. Any advice would be appreciated.

RE: DB schema help

I think you have a good idea about what attributes you need to have for each entity. I would do away with the UserEntry table and place a userID column (foreign key) in each of the other three tables.

For instance, do you want to know the USer who entered a Financial record? Regarding the appointment, do you need to know who it is for, or who entered it? Or who entered a journal entry?  

If yes, you need to have a UserID, UID column in those tables which will link to the User table.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: DB schema help

(OP)
Thanks for your reply. It certainly would make the joins easier with your approach. As is, I would have to programatically determine which tables to join based on the type of entry. A single SQL statement would be tricky. I was shooting for normalization, but I am no expert and probably could normalize even further. I might just go your approach, however, since it is a relatively small back-end.

RE: DB schema help

I would think that each entry would require a separate screen so it would be determined that way. Your way could be considered normalized if User Entry is a supertype and appointment, journal, and financial are subtypes of User Entry.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close