MySQL Forums
Forum List  »  Newbie

Re: I Must Be Stupid
Posted by: Rick James
Date: January 11, 2009 04:53PM

If you have under, say, 1000 rows, you could simply have
CREATE TABLE Master_info (
  business_type VARCHAR(...),
  ...
)
(plus necessary indexes; adding an INT PK is desirable, but not required)

But if you have lots more rows, "normalizing" as your describe is the "right" way to do it.

CREATE TABLE Master_info (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL,
  business_id TINYINT ...,
  ...
  PRIMARY KEY (id)
)
CREATE TABLE Business_types (
  business_id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL,
  business_type VARCHAR(...)...,
  possibly other info
  PRIMARY KEY(business_id),
  UNIQUE(business_type)
);
etc.

If you are using InnoDB, then you could add the various FOREIGN KEY CONSTRAINTS. These prevent certain types of screw ups, at a small cost in doing the runtime checking. Plus it documents the obvious (ie, that business_id is used for JOIN).

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.