large data storage

sfullman

Hi All,

I inherited a crude system from a client who receives leads including name, address, and email and phone #. Data is coming in by API pretty heavy, from about 15 clients, 24 hrs/day. Each client/project has a separate table. Each table has the clients project/name like jimbob, jaguar, mentor, etc.. Data is inserted in to the tables based on the clientid field passed.

Almost no error checking is done on the data.

Reason for multiple tables was he was getting locking up trying to do it in one large table.

The data is highly duplicate prone, and there are more than 3 million records in some tables and growing by the second.

My strategy right now is to NOT touch these tables, leave the system, and from them pull the data into a master table, de-duplicate them, and indicate instead that this record occured in this table at this id# through a join table.

You can imagine this table will be HUGE. It will have only very basic fields
fname
mname
lname
email
phone1
phone2
address1
address2
city
state
zip
zipplus4
country

and no more

MY QUESTION IS:
Of course I will normalize the city, state, and country. However, should I also normalize the firstname and middlename? (almost no data has middlename) you can imagine some of the queries that would be made and I just want to make sure that having an id for the first, middle, and maybe even last name is a good idea

And BTW, if any one knows any links to "working with reeeeally big tables for beginners", let me know 🙂

Samuel

sneakyimp

3 million records is not that many really. Assuming you have 10 bytes per field that's only about 400MB, right? If course when you multiply that times ten clients you get about 4GB which can be quite sluggish, depending on the computer.

Your records don't look that unreasonable. I might use separate tables for State and Country and just store a tinyint in my main table, but I don't really see much more need (or benefit) for normalization beyond that. I would probably add client_id so that I know which lead came from which client.

I would highly recommend that you check into table partitioning. This is a way to let you think of one big table while letting MySQL (or whatever DBMS you have) take are of keeping the big table broken into small chunks. You might partition by client_id. Or by state. You may find the wikipedia article useful reading.