DB schema help

[caoamo]

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.

 

[johnherman]

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

 

[caoamo]

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.

 

[johnherman]

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