Database structure help

[maUru]

I have two tables, machines and supplies. The supplies is about 5000 rows and the machines about 100.

Some supplies map onto more than one machine. Should I use a lookup table to map these onto the machines or should I use a string "mach1:mach2:mach3:mach4:mach5" at the end of each supply row which defines which machines they map onto and use explode/implode to manage these?

I would pick the lookup table, but in one scenario it comes out to half a million rows!!! Can mySQL handle that type of data quickly and easily? It would be a long list of 2 fields populated with numbers.

[MrAlaska]

Using a string like that is just wrong. l

With machines using multiple supplies and supplies routing back to multiple machines, you have a many to many relationship that does require an intermediate table. I would try to categorize them if possible, then you can make your category table the parent table, or do whatever you can to have one>many relationships. Your final design will, of course, depend on your specific needs. I cannot imagine setting up such a database without SEVERAL intermediate tables.

Of course, database design is not my greatest strength (although I am not sure what IS my greatest strength)

[mattuu]

Yes, it can handle one million rows, if designed right.

Make each table (machines and supplies) have an integer as a primary key,
a la

machines_idx INT(10) unsigned not null auto_increment primary key

Then you can create a match table like

CREATE TABLE machines_supplies (
  machines_supplies_idx INT(10) unsigned not null auto_increment primary key,
  machines INT(10) unsigned not null default 0,
  supplies INT(10) unsigned not null default 0,
  KEY machines_idx(machines),
  KEY supplies_idx(supplies)
);

But it won't REALLY be half a million rows unless MOST supplies map onto each machine. Let's say 100 supplies map onto each machine -- that's only 10,000. That's nothing for a table like the above. If it really were almost-all, you could be clever and make a table of all supplies that did NOT go to a given machine, and invert the set, but even that wouldn't be necessary.

I've had mysql tables at 750k rows which work still basically instantly performing most ops on their indexed fields, so it should be easy. With the above example, just store 1 row, taking the primary key from the respective machine and supplies table, and put those numbers in your lookup table as per above, and it should be lightning fast.

[mattr]

mattuu you have the right idea there, but drop the auto_incrament.

The table is fine as:

CREATE TABLE machines_supplies (
  machines INT,
  supplies INT,
  PRIMARY KEY( machines, supplies )
);

[maUru]

Thanks, I was going to do that anyway since to explode all the strings would probably take longer.

About the auto_increment thing, I have a database with various machines that get updated/deleted daily (for a 'specials' page). At the moment the id's go really high after a week or so and i get the dump, drop the table and reinsert the values, is there a better way to replace unused IDs?