Thanks for the question, Kuo Chao.
Asked: May 24, 2016 - 2:34 pm UTC
Last updated: May 25, 2016 - 5:54 am UTC
Version: Oracle 11g Express
Viewed 1000+ times
You Asked
0
down vote
favorite
Hello Tom, I am making a keep book web app. Every login user can record their expenses, and the user information records in USER table, however, I do not know which design is batter for the table EXPENSE as below, which is used to records user's expense item. I prefer using auto increment to create item_id for each entry, but should the item_id be increased separately by different user_id? or just increased no matter what the user_id is? Thanks for your answer in advance.
user_id(key) |item_id (key)|
1 |1 |
1 |2 |
1 |3 |
2 |1 |
2 |2 |
2 |3 |
or
user_id(key) |item_id (key)|
1 |1 |
1 |2 |
1 |3 |
2 |4 |
2 |5 |
2 |6 |
and Connor said...
With composite primary keys, I'm assuming a three layer structure here
USERS, pk=uid
USER_EXPENSE, pk=uid/expenseid
USER_EXPENSE_ITEMS, pk=uid/expenseid/itemid
For me, I'd just use a standard sequence for each, and not worry about ever ascending numbers or gaps. It's easy enough to assign a "display" sequence number for the items, ie
row_number() over ( partition by uid,expenseid order by itemid)
[where itemid is just a normal Oracle sequence]
On a related note, if you are building an application in Application Express, then composite keys can cause dramas as the number of columns increases, so with a normal sequence you could just easily make the physical primary key just the itemid.
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment