A Table for Every Website Member ... or not?

Question

A Table for Every Website Member ... or not?

john-530573

Grasshopper

Points: 12
More actions
December 29, 2010 at 1:55 am

#232812

Every member of my website will have one, or more, perhaps up to 5 or 6, ordered lists of items in his/her account. My first thought was to create a table holding the list item descriptions and each list item description's list item number for all the website members. The table design and data might look like the following - after a couple lists have been reordered:
MembNo ListNo ItemNo1 ItemDesc1 ItemNo2 ItemDesc2 .. as so on
278 1 1 Hammer 2 Nail
278 2 3 Saw 7 File
279 1 2 Screwdriver 5 Hammer
The member may want to alter the order of the item descriptions for display on their web page. For example:
1 Hammer
2 Nail
3 Wrench
might be changed by the member to:
1 Nail
2 Wrench
3 Hammer
My strong desire is to incorporate an AJAX ReorderList control within my web app. It allows the member to bring up one of his/her lists of items on a webpage and rearrange the order by dragging and dropping the items within a table on the webpage. Once a list item has been dragged into a new row within the html table (the mouse button then released) the database table is automatically updated. In order to make this work, I need that list of items within its own table. Its design and altered data might look like this:
ItemOrderNo ItemDesc
3 Hammer
1 Nail
2 Wrench
I'd be required to keep track of all of the ListTable tables for each member.
Let's say my website becomes very popular. Let's say, in a few years time, it boasts a membership of 250,000 and each member has, on average, 3 lists of 15 items. That's a lot of SQL tables. I'd love lots of members and their associated data but I'm wondering about the overhead of having so many tables within the database. Will it make a lot of difference? Perhaps I can keep the member's list data in one 'master' table and, should the member choose to use the AJAX reorderlist control to reorder one or more of his/her lists, that list data coud be retrieved from the 'master' table and be placed in a temporary table for reordering. The items in the temp table could be reordered and a stored procedure called to make the appropriate changes to the member's list data 'master' table data.
I read that SQL Server 2008 can handle a maximum of 2,147,483,647 objects (tables, views, stored procedures, etc) in a single database.
Your advice, please.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You already figured creating a new table per user will get you in trouble as soon as your app scales. It's great still having people out there thinking a step ahead instead of the "don't worry about later" mentality.

In your scenario you have a list with a (flexible?) number of items per member where the order information needs to be flexible.

Taking your current table design: What would you do if a user decides to need 20 items in one list (or one more than you already have defined)? In such a scenario you would have to add a new column and either use dynamic SQL to receive all columns or you'd even have to change your code each and every time your list needs to be expanded.

How about a table MembNo ListNo ItemPosition ItemDesc?

To use your scenario, you'd have the following rows:

MembNo ListNo ItemNo ItemDesc ItemPosition

278 1 1 Hammer 1

278 1 2 Nail 2

278 2 3 Saw 1

278 2 7 File 2

279 1 2 Screwdriver 1

279 1 5 Hammer 2

Within your app you'd need to keep track of the new item order and update the ItemPosition based on ListNo and ItemNo information.

If you'd need the data in a pivoted format as described you could use dynamic SQL to pivot the list.

Keep in mind the table design does not reflect the structure your app provided or receive the data. A relational db should follow the relational model.

I admitt, the design I provided above is not pure relational either, but it's a good compromise...

For reference only, here's the pure relational model of the given scenario (as per my understanding)

Lists (ListNo IDENTITY NOT NULL, ListDescription)

Items(ItemNo IDENTITY NOT NULL, ItemDesc)

MemberLists(MemberListNo IDENTITY NOT NULL, MembNo including foreign Key reference (FKR),ListNo incl. FKR)

ListItems(ListItemNo IDENTITY NOT NULL, ListNo incl. FKR, ItemNo incl. FKR, ItemPosition NOT NULL incl. check constraint for unique combination of ListNo and ItemPosition)

But I'd personally wouldn't go that far... hence the design posted first. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

john-530573 Grasshopper Points: 12 More actions · Answer 2

Lutz, thanks for your input.

I'm going to do my best to create a scenario that I do not need to create one or more tables per member, even temporary-type tables. My guess is that there is a way to make the AJAX ReorderList control work without requiring a table as I described in my prior post.

As I've thought a bit deeper about scaling my web app and database I've wondered how really large websites deal with millions of members. If SQL Server 2008 is limited to 2+ billion objects per database, do the Googles, Facebooks, etc, have the the same database running on multiple servers and each server handling the traffic from a certain geographic region thereby keeping the number of database objects to a manageable number per database? This is certainly a topic of it's own.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I am greatly concerned that creating ANY tables PER member is even on the table. I would be very wary of any design that has to have tables per user. You should use single use tables that are used for all members, I would also be careful about temp tables, if the data is small enough I would definitely use table variables within sprocs.

Also, I didn't see anything wrong with the suggestion of an additional field that carries order for a list, the first three fields represent a composite PKey which is not ideal but certainly makes good sense in this case.

CEWII

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

To add to what has already been said tables per user are probably not the way to go. Without knowing all the details you might want to normalize things a bit.

So, for example, you might have tables representing Members (Member) and Items (Item). Then you might have a table the defines the relationship between Members and Items (MemberItem). This MemberItem table could also have a column to represent the Display Order for an Item for a particular Member.

This model may or may not work for you depending on other details. Additionally, there are several ways to model this, this just happens to be a simple way to do it.