table schema

[BEFOR]

I have three tables that registered users insert to.

The 1st table contains the primary id associated with username/password/address and general info. (though by itself incomplete)

A) the main production table (not the "1st table) contains almost all foreign keys. With he primary id from the 1st table being carried onto this main table -- should I index this main production table on this primary Id? (and would I be risking referential integrity?) Reason for indexing primary id into the main produciton table: to use the 1st table only for initial inserts for new registrants and also to turn three way joins into two way (involving the mains table) and four-ways into three-ways -- resulting in less processing and easier join statements.

😎 the three tables mentioned, being inserted to by first time users (one of the three tables, the "1st" table" is being checked against constantly for username/password, etc). Is it a good idea for a site that is expected to be a high traffic site (SELECT statements/user updates/new inserts) to have these values split off into different table types (reasons included):

"initial tables" - in this case, the "three tables" where users only register for the first time, almost a staging technique. One of these tables (1st table) contains username/password. Values for "production" and "update" below(first row only) are pulled from these"initial tables.

"update tables" - to eliminate the possibility of users performing updates and insert simultaneously and to record histories. These are for update only.

"production tables" - where mainly SELECT queries are run. Columns are indexed.

The main reason for this is to allocate processing of transactions seperate from queries.

These three tables obviously contain some duplicate values but would be carried out in a definite order.

[AstroTeg]

Your 3 table setup didn't make too much sense to me. Are the 3 tables exactly the same and just duplicated based on "production" and "updates?" Also, what RDBMS are you using?

Maybe keep this in mind: All like data belongs together in the same table.

If you have a list of users, they should ALL be in the same table. No staging. Your RDBMS will have its rules on row locking and such and you'll need to write code to handle the locks and updates.

By creating seperate tables, you're making more work for your DB system. Index those fields which you will be querying off of the most often. Remember, if you index too many columns, you will just slow down your inserts (since each insert will require indexing work to be done).

If performance is mission critical, then you may need to look at optimizing the RDBMS server itself. Did you select the right RDBMS? Is it on optimal hardware? Do you have redundancy for when something goes down? Is it on a different server than the web server?

[BEFOR]

Your 3 table setup didn't make too much sense to me. Are the 3 tables exactly the same and just duplicated based on "production" and "updates?"

not exactly, some of the data is split off and stored on its own table. ex: maximum of 255 varchar text descriptions (three columns are needed on this for a 1) current description 2) description edit request by site users, and 3) approved description by site admin (along with date of approval/date gone "live") The last thing I want is a scan on these columns for the other values.

"update tables" are intended to maintain a history of updates.
ex: for renewals to each plan. new values are inserted while I maintain a history of each users subscriptions.

Also, what RDBMS are you using?

MySQL

If you have a list of users, they should ALL be in the same table.

One small problem I have is that there are three user-types. They may change identities (to some degree) but I think this is solved by linking to tables, like for runtimes:

RuntimeDR
RuntimeDR_ID | Runtimea_ID | Runtimeb_ID

(this contains links to "tablea" and "tableb" )

Here tablea gives runtimes of "plan a" subscribers while "tableb" gives runtimes of "plan b" subscribers. (lapses in runtimes would be another matter) But I've not tested this.

Same is done for billing and other identities/attributes.

[AstroTeg]

Poor database design there...

You have different flavor users. But they all have a name, right?

Store all their names in one table. They have different plans? No problem. You have a 2nd table describing all the unique plans you have. Each plan gets an ID. That ID and the user's ID can get stored in a 3rd table or if one user can only have 1 plan at a time, you can put that plan ID in the user's table.

Doing this will have better performance (in most cases) and will be easier to maintain and update later on.

[BEFOR]

Please ignore the original post for a moment, and would appreciate a review of the following as this is the core design.

There are three tables used to register new users
(detailed information inserted from three seperate forms, a little exhaustive, I've found its best to break into three seperate forms - also for what's natural appropriate to ask at each stage, etc, of each form ) They are:

1) Subscribers table- inserted is name, address, phone, fax, 2 email addresses, username/password, and more.

2) Subscribers Service table - inserted is detailed information about their company's insurance, licensing, coverage amounts, insurance carriers, exiration dates, sales figures, etc)

3) Subscriber plan table - inserted is the type of plan they request. There are one type of plan (which has three different grades/prices for), and another type of plan entirely. Also inserted is a 255 varchar company description. Too is a series of drop menus to define the services they perform, up to six maximum (limited by 6 drop menus).

Relationships of both types of plans are considered many to many and values from the 6 drop menus are inserted into table:

service category plan a
Subscribers Id | work category

///later when reviewing the user request, the site admin echoes these 6 max. values onto another like table. note that this table will have up to 300 values, not six, and must be kept seperate from plan a - the orders may become confused between the two tables during altering updates)

service category plan b
Subscribers Id | work category

Each subscriber can belong to plan a, plan b, or both.

The original post was an effort to set new tables since these three tables will receive many inserts (will tend to not index these columns)

Lately I'm considering making the 2nd and 3rd table above the only ones of their kind (perform inserts,updates, deletes, queries on) to simplify the structure and with '(1) Subscribers table' having id/username/password which will be constantly checked against.

So as it is, the 'plan id' is resides the "3) Subscriber plan table" which of course is linked to the 1) Subscribers table

Suggestions? Should the plan id be in its own table (not in same table as 255 char company description column of table 3)?)

[AstroTeg]

It sounds like you could use a little more normalizing (2 fields for email addresses should be just 1 table with all the email addresses - possibly the same with the plans, but not enough info on plans to determine for sure), but what you have seems to make sense and look good.

[BEFOR]

AstroTeg , much appreciate your response.