PHP and DB Design: Store data in multiple tables and merge or replicate data

I'm building an application where every user will have a bunch of items. When a user signs up about 150 user specific item are added into a table for every user from a template table.

Logic as follows: (copy)

User signs up

Copy 150 template items (rows) from template_table to userItems_table

Display items from userItems_table

The user can change his items (name, price, color) as he wishes in the web UI. Also add new ones, delete existing ones etc.

The question is now.. instead of copying the 150 rows to a user items table.. I could instead just use the template table and when a user accesses the items in the UI I'd do two select statements, then in PHP merge the two arrays to get the users specific items.

Logic as follows: (overlay)

User signs up

If user changes an items, store the diff into userItems_table

Select template_table and userItems_table in to two arrays, merge the arrays with an algorithm

Display items from the merged array

The application is expected to grow with around 20 user per week. So in a year there will be around 1000 active users. Equaling at least 150.000 rows per year plus a number of user specific changes. They access the items frequently in the App but they are not changed often. Read ratio a lot higher than write 开发者_运维知识库ratio.

My objective is to have a sustainable and scalable design for this.. because a lot of other functions in the app depends on accessing a users item list.

What is the best way of doing this? Any creative ideas?

Thanks!

Additional info:

• Database used is MySQL 5.1 with InnoDB
• Template items will not change during the lifespan of the application.

One version, you make copies of the template set, the other way, you set up an “overlay”, where everyone shares the template except for what they’ve changed or added.

Some discussion points to consider.

Performance: In the Copy version, users go to one table to get what they need ( = 1 table hit). In the Overlay version, you always have to go to both tables, first to get what the users’s entered, then to get what they haven’t entered (= 2 tables hits, as per @Renderlin’s null-checking algorithm).

Template Modification over time: what happens when, after T time and N usrers, you decide to add some items to the template. In the Copy version, existing users do not get the new items, while in the Overlay version every user immediately picks up that new item. Similarly, if you choose to change (or drop) a template item, if the user has never modified the item, it is immediately modified for them.

I’ve insufficient information to figure what’s effective and what's not--this must be determined based on your business requirements.

Assuming you go with the option of keeping a template table and then only putting records in the user table for the items that user customized... how about instead of merging the template with the items the user changed, instead do a left outer join.

e.g.

select nvl(uit.item_id, itt.item_id) item_id, nvl(uit.name, itt.name) name, nvl(uit.price, itt.price) price
from item_template_table itt
left outer join user_item_table uit
  on itt.item_id = uit.item_id

NVL is an oracle function that returns the first parameter unless it is null, in which case it returns the second. If you don't use oracle you can still get this same functionality using a case statement or using some proprietary function of your database.

That way every record coming back is the user's overridden item if it exists, else the template one (assuming no null values are allowed in the user_item_table... if so, then you need to change the query not to do nvl on each column but to check if the primary key in the user_item_table to see if it is null... if not, then take all the values from the user_item_table.