MySQL: Organization of a large database-- How to do it *right*?

Question

I'm setting up a database to contain all of the invoice and job file information for the company I work for. Each job usually consists of 2-4 items listed with parts numbers and so forth. However, some require up to 20 or more items.

How could I *correctly* organize my MySQL database (or any DB for that matter) so that there are fields available for those many items without having tables with 30-40 fields? Does MySQL have any feature for 'sub-tables' that are adjusted by list, not by field?

One idea I had, which seems inefficient, is to have the system create a NEW table for each order... and then have each table a customized number of fields based on the requirements of that particular order. Is that a good idea?

Well... I see where I threw you off. We don't have specific part numbers-- we design and build custom parts per our clients orders. So... we don't have a part number like that-- instead, our 'part' number is actually a drawing number (a cad drawing of the part). So... not much overlap at all. One client would never order another client's part. Make sense?

Answer 1

What you need is an extra table that ONLY assigns part numbers to jobs. (If I understand your question right; I'm confused by the "2-4 items" reference.)

CREATE TABLE part2job(recnum int, job int, part int);

So if job # 1 uses parts 3, 4, and 5; and job #2 uses parts 7 and 8, your table would look something like this:

[recnum, job, part]
1, 1, 3
2, 1, 4
3, 1, 5
4, 2, 7
5, 2, 8


Added in responses to your additional details: in my explanation above I referred to 'part numbers', but instead of part numbers, you could/should read 'record numbers of parts' or 'drawing numbers' or even 'record numbers of drawings'. The point is that you have three tables: customers, parts, and one extra table that links the customers to the parts and v.v. That extra table would have at least two fields (in the example above I added a third field containing a unique record number so each record in that table would be unique); the first field would point to (the record of) a customer, and the other field would point to (the record of) a part. Since you can have any number of records, it is a breeze to connect any number of parts to a particular customer, with VERY little redundancy.

Feel free to mail me if this still isn't clear: I could send e-mail you a drawing that might make it clearer. My e-mail address at yahoo.com is the same as the nick I use on this site.

Answer 2

i have self assurance you may upload a csv document utilizing device like phpMyAdmin. that is what I frequently do. for example, if i want to bypass content fabric from database A to database B, i take advantage of mysqldump from command line like this: mysqldump databaseA > a.sq. then i will load it into B: mysql databaseB < a.sq. The instructions above are only examples and also you also want to provide arguments like person call and password.

Answer 3

You really need to normalize your data. there is an into here: http://www.informit.com/articles/article.asp?p=27785&rl=1

Normalizing means to make your database efficient without losing data should you delete a record

For invoices, you would have a customer detail, invoice header table, invoice detail table and possibly an invoice footer table

It is a very bad idea, from both an efficiency and management standpoint to maintain hundreds of tables....because hundreds become thousands and if left unchecked becomes a monster and would need to be redone.