Big table or multiple separate tables? (database design question)

Question

This is a database design question.

I want to build an invoice web application, an invoice can have many items, and each user can have an inventory list of product items that they can store and choose to add to an invoice item.

My questions are:
1. Should I store all product inventory for all users using my application under one single table? Or have a separate product inventory table created for each user?
2. Is this even possible?

1 table is easier, but what if this single table grows too big, will I have a problem? (primary key INT).

Answer 1

Are the products user generated or do you control them?

At any rate, I would think you would have one table for products and one table for users. Depending on the source of the products (in other words, if they are exclusive to and perhaps loaded by the user or if they are accessible by any number of users), then you would potentially have a table that maps users to products. If the products are indeed exclusive to a given user, then merely storing a user ID along with the product record would be sufficient.

Don't worry about table size, at least not initially. SQL Server is a powerful database tool, just be sure you have good database normalization and proper indexing.

Answer 2

As the table grows the setup and layout of your server will begin to matter more as well as your choices of indexes and the way you write your queries. However, unless you expect to have many millions of rows, no, you will not have too many rows for one table to perform well.

A general rule of thumb is that tables should contain data, but not be data in and of themselves. If you start having the table indicate who's data it is you are possibly veering into the 2nd category and need to back up and consider what you're doing.

Answer 3

A table per user is a bad idea. Keep all inventory in a single table, keyed on userid. The table would have to be pretty massive for this to be a problem on any industrial-strength DBMS (you should wait till you have tens of millions of rows before even asking such questions).

If you most commonly access inventory by user, you can speed up such queries by making the userid the first column of a clustered key, thus forcing inventory per user to clump together on disk. Again, though, don't even ponder these issues until you observe an actual degradation in performance.

Answer 4

In the case where each user has a distinct set of products they own/manage, a single table can be partitioned later on. As others suggested, you should not worry about performance unless you expect tens of millions of products.

I'd suggest starting off with a single table. If the product schema is identical for each user, this is a very simple and efficient design.

However, if products related to different users require different schema, you could end up with a very sparse table (a lot of empty/NULL fields in each record, which will affect performance for everyone).

One common approach to this is an EAV (Entity-Attribute-Value) table, which has three columns: product id, attribute name, and attribute value. This is a completely dynamic solution and is very simple. However, it makes implementing declarative constarints difficult.

My preferred approach for a dynamic schema is to make the static/always required fields part of the permanent/table schema. The dynamic part can be created as an XML field and constrained by an XML schema (or a collection). Data integrity can be enforced well in this way and you only require a single field for the extra bits.