Re: Question about schema

Clark,

Option 2 is a denormalized nightmare. Unscalable and a bear to admin. Very contrary to how rdbms engines work best.

Use option 1, and index appropriately the 'second' table (the address book entries) depending on the nature of typical application/user access. For example, use a fk in the fact table that is contained in the 'first' table (the owners of the address books) for data integrity and index the 'second' table on the fk for performance.

You could add more indexes, depending on usage, which could improve performance of selection activites, however they will slow down insertion/update activities at the same time.

Good luck.

--
Michael O'Neill
mjoneill_at_email.com

"Clark Snowdall" <clark_at_e247.com> wrote in message
news:3A170759.8DA61243_at_e247.com...


> I have a quick question to pose regarding database theory.  Imagine if


> you will a company that needs to keep track of 1 million users' address

> books.  Is it best to:

>

> 1)  Have a master table listing all the users' principle information

> (succh as username, password, real name, address, preferences, etc.),

> and then also have another table listing all of their address entries.

> This means of course that if each of the 1 million users have 10 entries

> in their address book the second of the two tables can reach 10 million

> records.

>

> OR 2)  Again have a master table listing all the users' principle

> information, but instead have a new table for each user that only lists

> the address book entries for that user.  This means of course that there

> would be 1 million tables, each with 10 entries each.

>

> Of these two scenarios, which would the most efficient for searching,

> which would be easiest for updating, and which would be the easiest to

> administer (providing that the application can perform the simple

> administrative tasks such as creation of deletion of these tables).

>

> Thanks for any info on this issue,

> Clark

>

>