Mark Hensler — Wed, 19 Feb 2003 01:39:02 +0000

You could make an index table, which would have the client info and the name of their table. Then each client could have their own table with their address book data in it.

It may be messy, but it will search a lot faster. And remember, mySQL has limits to table sizes (2GB if I remember right, but it depends on your OS).

ROB — Tue, 18 Feb 2003 18:02:30 +0000

well, mark, i've considered giving each client their own table, but there will be thousands of individual clients (and currently is over 100) For some reaon 100'sor 1000's of tables (or God forbid databases) just doesnt sit right with me.

Mark Hensler — Tue, 18 Feb 2003 17:47:29 +0000

You could also give each client their own DB.

mairving — Tue, 18 Feb 2003 03:10:38 +0000

Well, I guess if I would have read the part about importing their address book, I might have answered better. I don't know how you could make the database any better since it has to be made to import all of the address book garbage. The only way that I could think of to make the data sort faster is to limit the table to a certain number of records. You could possibly do this by having tables based on a alphabetically range. For instance, a table would only contain records A-C, another D-G, etc. That is all I can think about at the moment.

ROB — Mon, 17 Feb 2003 16:26:03 +0000

no no, these lists are imported hundreds or thousands at a time, not entered one by one. user's can export their outlook and excel address books as csv and import them.

mairving — Mon, 17 Feb 2003 16:23:57 +0000

Quote: Originally posted by R0B
well, the reason for that is the user is uploading their own address lists, and something like 'state' could be 'FL', 'Florida', 'The Sunshine State', 'where mom lives', or anything for that matter. I can't restrict or modify the data that the user uploads.

Sure you can control the data that goes in there. Instead of using a textbox, use a Select list of names pulled from the states table. So the only allowable entries are those that you allow not whatever value they want to put in.

ROB — Mon, 17 Feb 2003 15:32:17 +0000

well, the reason for that is the user is uploading their own address lists, and something like 'state' could be 'FL', 'Florida', 'The Sunshine State', 'where mom lives', or anything for that matter. I can't restrict or modify the data that the user uploads.

mairving — Mon, 17 Feb 2003 15:28:22 +0000

It looks like your first table could be broken down into several more tables. The other thing is that most of the fields are varchar(50) when they don't need to be. Why would state need to be varchar(50)? Ideally it should be in a separate table, something like state_id int(2) primary key, state_name varchar(20) in the state table and state_id int(2) in the other table.

Keep in mind that good database design is avoiding redundancy in tables. Instead of having 500 records in your main table that say 'Tennessee', you could have 500 records that say 32 or whatever the id of the state is. This also helps to avoid entries in the state field like 'Tn', 'Tenn', Tennessee', 'Tenesee' and the like.