5

I am designing database architecture. I have user. User make request for order. Order is associated with payment. Once payment is completed, I want to generate sticker for that user.
Sticker has initial prize(i.e. $10). Now, admin can edit sticker prize. so, if admin change sticker prize then order will generate with new prize after change by admin.

By database architecture is as follow :

User(id, name, email, password)
Order(id, user_id, no_of_sticker,sticker_prize, address, status)
Payment(id, order_id, amount, date)
sticker(id, order_id, name, content)
sticker_info(sticker_prize)

Now, my question is--- is it good to create new table for just one single attribute. That sticker_prize is only available for admin to edit

Please give your valuable suggestion.
Thanks in adv.

Ketan Ghumatkar
  • 720
  • 7
  • 14
  • It seems to me that someting is missing in your sticker_info table - it should have the id of the sticker as foreign key, right? Or do all stickers have the same price? Then that is the point you are missing, a sticker-category and the related price for that... – Argeman Apr 22 '13 at 09:33
  • All sticker is having same prize. There is no different categories of stickers. – Ketan Ghumatkar Apr 23 '13 at 05:03
  • What is the point of the 'sticker' table? Why is the order_id stored in it, especially as the sticker_id is stored in the orders table? Why can't the administrator edit the sticker price which is in the orders table? – No'am Newman Apr 23 '13 at 05:41
  • because order is given by user. I have added tag_prize in order to keep record of tag_prize of previous order if in time admin change it. Order table gets entry if order is set.To set order tag_prize has to be there in database previously. Hope you are getting... – Ketan Ghumatkar Apr 23 '13 at 07:37
  • Are you saying that at any given time there is a "current sticker prize", and when an order is created it gets the sticker prize in effect at that time? e.g. Admin sets sticker prize to $10. User A creates order, prize is $10. User B creates order, prize is $10. Admin changes prize to $15. User C creates order, prize is $15. Etc. Or does the admin change the amount of the prize on existing orders? – Jay Apr 25 '13 at 17:26
  • BTW What is "no_of_sticker"? Is that sticker.id, or something else? And why are no_of_sticker and sticker_prize in order table rather than sticker table? That seems a little odd. – Jay Apr 25 '13 at 17:28
  • yes Jay...You say right.No. Admin does not changes prize to on existing order. Admin changes the prize of sticker and then it will be application to next order after that. 'no_of_sticker' is the number of stickers user order. I have keep the sticker prize and number of sticker in order because it is as per order. Sticker may have different prize as per the admin change and to keep record of previous tag prize at the time of order, I hav e keep it in order. – Ketan Ghumatkar Apr 26 '13 at 09:55

1 Answers1

2

Creating the sticker_info table, for the purpose of storing a single value is ok from a database design perspective. You should ensure you have a primary key on the table so you can not get duplicate rows.

In larger systems, there are often lots of values like this, and often the solution is a table like: configuration( configId, configValue ).

WW.
  • 23,793
  • 13
  • 94
  • 121