Re: DBA performance and modeling question

SRK I actually think there are several questions hidden in one here.

You used the word "model" several times. Do I take it that you are fairly sure that these are in the main distinct real-world entities, whatever you actually do with them?

Let me give an example to explain what I mean by my question. My database may deal with a number of people in different roles: Customers, Suppliers, Consultants. I hold very similar - nay, the same - information about each of them but I know my business views them very differently.

In this case, I would suggest you do exactly as you have done: you model them as distinct (although the question as to whether they are in fact sub-entities of a single type is a moot point).

However, designing a database is a different process from modelling a business. In modelling the business you are concerned to understand how the business works and the model should be fully normalised. (How normalised is fully?) In designing a database you must meet all requirements of the business but, within that basic constraint, you must maximise performance. As long as you understand the implications of any compromise to the model (and as long as the business owners buy into that compromise), do what you think is sensible.

In my example, it may be reasonable to put all roles in a single table by adding a flag column to idicate whether they are customer, supplier or consultant. Note, however, that there are *always* implications of any decision like this: just be clear about them and that the business users are satisfied that they will not compromise any current or sensibly foreseeable future modifications.

Once this distinction is accepted, some of the comments of the other replies apply. There is no right answer.

Len

>I have a situation where we have exactly same table for different types
>of objects. The question is should we model it as multiple tables even
>though the columns are same or should we model it as one large table and
>have "type" as a column which distinguishes the records. The criteria
>we are evaluating is the table can become huge with e-commerce sites.
>We are assuming this table is going to be huge with 20-40million
>records. If we design it as multiple tables, there are 4 types, which
>could break down to 5 -10 million records per table if we keep them as
>separate tables.
>
>In terms of database design I like it as one table but not sure if
>having many small tables will be any performance benefit vs one big
>table. Most of the queries will be using index search.
>
>Thanks in advance.
>SRK
>
Received on Sat Nov 18 2000 - 12:08:51 CST