mysql - table -column setup

[mudmanc4]

Yes this is vague because I'm more used to editing aside from designing databases.
 
I'm working on a database (new) where right now it's not going to be used for much aside collecting basic client data ie~

$aa->whatever-name(
	'table_name',
	'column1, column2, column3, column4, column5, column6, column7',
	':Name, :Email, :Subject, :Phone, :PhoneExtension, :Website, :Message',
	array(
		'Name' => $Name,
		'Email' => $Email,
		'Subject' => $Subject,
		'Phone' => $Phone,
		'PhoneExtension' => $PhoneExtension,
		'Website' => $Website,
		'Message' => $Message
	)
);
	

Later I plan to insert this data into a form , then insert the form into a customers account, as well as add column(s) for other obvious client information.

 

My question is knowing this later will be heavily used, would I be better off creating a new table for each user / client than this way. I'm talking performance reasons.

 

I've got one chance to get this right , as I say right now this will only collect new clients as they submit a form for a quote, later I will have to create a script to manually add thousands of clients and their current as well as past customer information. Daunting task ahead , so I  must get this at least close to as simple as possible.

 

 I'm aware this is a basic sql question , but I don't think it will be for long.

[CA3LE]

For a simple layout like that, for that information. I'd do a single table.

The reason you'd want to split them up is if there was a lot of information stored for each person or the overall database was expected to have hundreds of thousands or millions of entries.

Examples on TestMy.net where I split the databases into individual tables would be the host, city and country databases. Having them split allows me to run batch queries that split the load of complex operations... on really large amounts of information if they were stored under one table when there is an issue it causes everything to fail. Splitting it allows separation, if a table becomes corrupt it doesn't cause the entire database to fail. You also don't have to worry as much about locking during backups and stuff because the tables are smaller... so if they do lock it's only briefly. Usually not effecting the client.

In the example where I split my tables there are about 100,000 tables of information each of which has up to 100,000 entries (a batch program comes in once a day and prunes the tables for me... keeping queries very fast). The master database I have is a single table and has MANY millions of entries. But in this instance a single table fits the bill because it's highly indexed for it's purpose. But if I were to try to use that table for getting individual host, city or country averages... it would crash the server in a heartbeat. Even with 24 cores... the server would spiral out of control. There's just too much information to process. So I store my inf.... well, your information in about 10 different ways so I can query more efficiently.

You're smart for planning ahead. You have to when you build databases, you have to picture not only what you need right now but also plan the growth of the database. To tell you the truth, I probably don't do it the best way. I've just found what works for me. With mySQL though, as with php, you make of it what you want to. How you layout your design is totally subjective to your application. There really is no right or wrong way to do it. But there are more efficient ways. Trick is to find them. ... I think you'll be able to efficiently do that with one table. Index the fields that you query heavily and it should be very fast for that purpose.

-D

[mudmanc4]

That's very good information , thank you.

 

 There will be quite a bit of information on each client gradually. Including job numbers dates started / completed , costs involved and details such as addons / revisions, time to cost , project addresses, geographical locations , job contacts / supervisors, administrators and their details, and a plethora of other client to job specifics, multiple jobs same client different details.

 

Basically I'm taking a 50+ year old company that has never had anything about their clients on a server, and digitizing it, as well as getting the application sorted so it can be accessed initially my myself, then secretary, phone personnel and end user accessing different portions.

 

Starting with new clients on one database you think is better than trying to constantly stay ahead of issues while new entries are coming in?  At the same time , propagating the new data to a second database would be fine , so long as i don't get off on a tangent and structure the second differently from the initial that it causes me more work importing. Which is generally how things happen as I learn more of what I need to have compared to what I want lol

 

Just sayin this could snowball on me very quickly into a disaster setting me back in time I do not have.