Shawn,
I'm not certain you are saying otherwise (or simply recommending against it), but I want to point out that mysql can handle separate auto_increment series for each client_id with MyISAM or BDB tables. You set up the table like this:
CREATE TABLE messages (client_id UNSIGNED INT NOT NULL,
msg_id UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (client_id, msg_id),
other columns ...MySQL will keep a separate count for each client_id. Of course, this has the disadvantage you pointed out of requiring 2 columns to reference rows.
See <http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html> for details.
Michael
[EMAIL PROTECTED] wrote:
No, you aren't asking too much of MySQL in most of your questions but in another question, you are. Let me explain.
PRIMARY KEYs are nothing more than UNIQUE INDEXES on a table. How many columns it takes to uniquely identify any row of information depends on your data structure. Many people use an auto_incrementing INT (or BIGINT) column to artificially generate uniqueness. That means that their PRIMARY KEYS only require one column. Others have the requirements that certain combinations of columns cannot appear more than once in a table. They designate all of those columns as their PRIMARY KEY. Here's an example that combines elements of both (for the sake of this sample, this is NOT a normalized data structure but it will be highly indexed)
CREATE TABLE us_buildings (
ID int not null auto_increment.
State varchar(30) not null,
City varchar(30) not null,
County varchar(30) not null,
Road varchar(45) not null,
AddressNum int not null,
AddressSuffix varchar(15)
PRIMARY KEY (AddressNum, Road, City, State, AddressSuffix, County)
UNIQUE (ID),
Key(Road, City, State, AddressNum),
Key(State, County, City) Key(County, State, City)
)
This table is structured to ensure that any single structure in the US can be uniquely identified by it's position (AddressNum + AddressSuffix) along a road. The multiple-column PRIMARY KEY is used to prevent duplication and also serves as an index. The UNIQUE index on the ID column prevents anyone from changing the column into a duplicate value. That makes this column a good column to reference from another table (maybe a table like "resident" or "surveylog") without needing to duplicate all of the textual information it takes to identify the structure. The additional indexes are defined so that common searches on combinations of certain values will execute quickly. In a properly designed data structure, each element of that table would have their own table and this "fact table" would be using only the ID values of the other tables. That change (normalization) will make a HUGE difference in both storage requirements and index performance of this example as the database engine will retrieve numeric values directly from the index without the need for an additional seek (or two) to read the value from the table data and you will have nothing but numeric indexes on this table.
In your case, you can still use an auto_incrementing ID value as a reference value and your PRIMARY KEY can be composed of a combination of (client_ID, client_msg_num) but you will have to mange your per-client message numbers manually. It would be rather difficult to build into any database a management system that automates the process of creating an auto_incrementing value based on the unique combination of values in one or several columns.
Just before you insert a new message, you would need to check for the MAX(client_msg_num) for the client_id creating the message and assign the new message the next larger number. You would use transactions (InnoDB) or table locking (all table types) to prevent a second instance of your application from doing the exact same thing at the exact same time (if you don't serialize your access to your message numbers, you could accidentally issue duplicate numbers). Some DBAs would design a second table that stores the most recently used message number for each client_ID (I have used both techniques). Which method works best for you will depend on your benchmark testing.
I hope you see that MySQL does most of the work for what you want to do but not quite all of it.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Justin French <[EMAIL PROTECTED]> wrote on 09/02/2004 12:31:57 AM:
Hi all,
I'm designing a hosted application that many clients will access. I've got all sites running off one PHP code base quite nicely, and now I'm hoping to reduce the multiple databases down to just one shared DB.
(Note: at this stage, all accounts are fictional, so there's no legacy data to worry about).
So instead of each account have it's own database and autonomous tables, I'd like all accounts to share one database. This will:
- make updates easy - allow each account to be "group aware" - allow a parent website easy access to all account data
I've added client_id columns to all tables, and things are working fine, with the following exception...
I'm worried about the primary keys (IDs) for each table growing to an unmanageable size pretty quick... 50 accounts * 500 forum posts = 25000 IDs pretty quickly. 50 sites * 5000 members = 250,000 members.
I'm sure this isn't much of a technical/performance issue (with proper indexing), but it's just strikes me as a little strange that the first news post for an account could have an ID of '502846', rather than '1'.
So, I was hoping I might be missing something in regards to primary keys...
Can a primary key (or unique key) be established on two columns (eg primary key = client_id + post id)?
Can an auto-increment column only increment in relation to the client id?
Or am I expecting *way* too much out of MySQL and relational data design?
--- Justin French
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
