Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DB schema help

Status
Not open for further replies.
caoamo

caoamo

Programmer
Oct 8, 2005
29
0
0
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Ashok Kumar J
  • Locked
  • Question
i am not able to print report on barcode printer.... please help!..... it does not print at all
Replies
0
Views
79
Ashok Kumar J
Ashok Kumar J
Andrzejek
  • Locked
  • Question
DB Guidelines
Replies
17
Views
250
Olaf Doschke
Olaf Doschke
Andrzejek
  • Locked
  • Question
Manager issue in DB
Replies
8
Views
157
Olaf Doschke
Olaf Doschke
gennaroalpha7
  • Locked
  • Question
DB Dev
Replies
7
Views
141
gennaroalpha7
gennaroalpha7
gennaroalpha7
  • Locked
  • Question
DB Tables Design Question? 1
Replies
6
Views
132
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top