I have to create a database for a software product used by more trading companies. Each company must have the same table schema.
How is better to do this?
I'm thinking for two possibilities:

#1
Create one table schema and in each table add a column CompanyId.
But there will be lot of records and I think the queries will run very slow.

#2
Create a table schema that will duplicate for each company.
CompaA_Users(.....)
CompA_Products(....)
.....................

CompB_Users(.....)
CompB_Products(.....)
.............................

Which proposal is better?
Is there a better solution?

Thank you.

#1 is far better

index your tables properly and there will be no query performance problem

#1 gets my vote too!

Also, it is worth noting that you do not necessarily need to store the company in every table as you can limit the resultsets using appropriate joins between tables.

As above. Each table that has company specific data should, as mentioned, include the CompanyID column. Make that the leading column in your clustered index, and (obviously) ensure every query filters on the CompanyID. The size of your table now is largely irrelevant - each company's data is grouped together and accessible via the B-Tree.

pootle flump ur codings are working excelent.

I hadn't thought about adding it to the CI! Very good tip ++1

Poor man's partitioning The point I never got rjberabarovovov to "get".

pootle flump ur codings are working excelent.

Thank you very much for your quick answers!

Ugh! No. Use surrogate keys and avoid storing company ID at every level in your schema.

Q: Show me all the products my company provide.
A: Scaaaaaaaaaaaaaaaan.

Q: Show me all the users in my company.
A: Scaaaaaaaaaaaaaaaan.

pootle flump ur codings are working excelent.

Sorry poots, I'm with blindman on this. I'd rather be able to use an index on the company id in a very small table then join using index seeks on a highly selective surrogate key than to do the table scans you are advocating. If you build your queries correctly, you could use the clustered index you suggested which would allow you to at least do an index scan instead of a table scan and that could be nearly as efficient as using the surrogate keys.

-PatP

What makes you think that would force a scan when a query against a clustered index wouldn't?

I'm advocating avoiding scans.

I'll whip up some code, mebbe at the weekend - busy week. I'd like to bottom this one out since this is a default design decision I make for this sort of database.

To be clear - I am advocating this sort of physical design in the specific circumstance where a single database is designed to store distinct and discrete data sets in the same domain. These datasets are never to be treated as one, always separately. In fact, if the data of two of these sets were to get retrieved together it would indicate either an error or a security breach.

I am not preaching a one-size-fits-all dogma which is the de facto position of most people on the surrogate-natural key divide.

The last two databases I released were of this nature (one a survey database, the other hosting multiple clients' data). Using this design even complex queries are jam packed full of seeks and merge joins. No lookups, scans, hash joins. Absolutely zero superfluous logical reads.

I don't see how it isn't obvious - all "like" data is clustered with all related data. It is essentially logically & physically partitioned. Why on earth would you want to stuff a page full of 20 different clients' data when a client should only ever access their own data?

pootle flump ur codings are working excelent.