I'm fairly inexperienced at database design, so I am trying to plan my database beforehand as much as possible. This database is to hold information about a VMware environment.

The database has 9 tables currently. Currently the idea is to truncate most of the tables daily and then use LOAD DATA LOCAL INFILE to populate the tables from CSVs scraped from each VMware vCenter. I'm using auto-increment columns for primary keys, though I could use composite keys consisting of the object's ID and the vCenter name (the object's ID is only unique per vCenter) if I were more confident in my database knowledge.

Since most of the tables will be truncated daily, is there any benefit in using foreign keys rather than just using WHERE logic? Are there other questions I should be asking that I might not know to ask?

  • 2
    What does truncation have to do with primary and foreign keys? And the WHERE logic? – mustaccio May 31 '19 at 16:02
  • 2
    Foreign keys are more about integrity of the data. Child records without a parent don't happen if foreign keys are used. – WillG May 31 '19 at 16:24
  • 2
    As for primary keys, NEVER go without them. – WillG May 31 '19 at 16:27

PRIMARY KEYs are important; use them. Since there seems to be a 'natural' PK, use it instead of a surrogate AUTO_INCREMENT.

FOREIGN_KEYs are not a requirement. They do, however, create helper INDEXes, but you could create those without FKs`.

If the data is changing every day, what would you do if an FK failed?

Using TRUNCATE, then LOAD leaves the table empty for a brief time; if that is a problem, do this:

CREATE TABLE x LIKE real;  -- or use a regular CREATE.
RENAME TABLE real TO old, x to REAL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.