Search posts, tags, users, and pages
u use workbench -> you can click on the icons on the left side 1:n, n:m ... and than on the tables and it creates the releations for you. btw pls define what the foreign keys should do -> on update CASCADE for example :)
Hi j, thanks for the reply!
I tried that right before I posted and it created new foreign keys named like game_gameplatform instead of using the columns I intended. This actually touches on the problem I've been having getting the constraints to work.
When I run the scripts just like they're written above, instead of making actual foreign keys where I specified it makes indexes. And when I click on the Foreign Key tab on any of my tables it says I have to use a storage engine that supports foreign keys like InnoDB.
I got looking into it and I ran SHOW TABLE STATUS WHERE Name = 'Game'; on each of my tables and they all say InnoDB in the Engine column. I have the latest version of the community edition installed, MySQL Server 5.7 and Workbench 6.3.
Still not sure what's going on with that, but it won't let me do anything on the Foreign Key tab on any of my tables.
Jay Ellsworth I will check it later tonight and try to send you a link to a workbench file so you can check against my "version" :) and apply it if needed :)
-- -----------------------------------------------------
-- Table `Developer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Developer` (
`DeveloperID` INT UNSIGNED NULL DEFAULT NULL AUTO_INCREMENT,
`Developer` VARCHAR(80) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`DeveloperID`));
-- -----------------------------------------------------
-- Table `Publisher`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Publisher` (
`PublisherID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Publisher` VARCHAR(45) NOT NULL,
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`PublisherID`));
-- -----------------------------------------------------
-- Table `Tag`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Tag` (
`TagID` INT UNSIGNED NULL DEFAULT NULL AUTO_INCREMENT,
`Tag` VARCHAR(45) NULL DEFAULT NULL,
`isGenre` TINYINT NULL DEFAULT 0,
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`TagID`));
-- -----------------------------------------------------
-- Table `Platform`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Platform` (
`PlatformID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Manufacturer` VARCHAR(45) NULL DEFAULT NULL,
`Platform` VARCHAR(45) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`PlatformID`));
-- -----------------------------------------------------
-- Table `Game`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Game` (
`GameID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`Game` VARCHAR(45) NOT NULL,
`ReleaseDate` DATE NULL DEFAULT NULL,
`DeveloperID` INT UNSIGNED NULL DEFAULT NULL,
`PublisherID` INT UNSIGNED NULL DEFAULT NULL,
`Comments` MEDIUMTEXT NULL DEFAULT NULL,
`Thumbnail` BLOB NULL DEFAULT NULL,
`Backsplash` BLOB NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`GameID`),
INDEX (`DeveloperID` ASC),
INDEX (`PublisherID` ASC),
CONSTRAINT ``
FOREIGN KEY (`DeveloperID`)
REFERENCES `Developer` (`DeveloperID`)
ON DELETE RESTRICT
ON UPDATE CASCADE,
CONSTRAINT ``
FOREIGN KEY (`PublisherID`)
REFERENCES `Publisher` (`PublisherID`)
ON UPDATE CASCADE);
-- -----------------------------------------------------
-- Table `GameTag`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `GameTag` (
`GameTagID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`GameID` BIGINT UNSIGNED NOT NULL,
`TagID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`GameTagID`),
INDEX (`GameID` ASC),
INDEX (`TagID` ASC),
UNIQUE INDEX `GameID_UNIQUE` (`GameID` ASC, `TagID` ASC),
CONSTRAINT ``
FOREIGN KEY (`GameID`)
REFERENCES `Game` (`GameID`)
ON UPDATE CASCADE,
CONSTRAINT ``
FOREIGN KEY (`TagID`)
REFERENCES `Tag` (`TagID`)
ON UPDATE CASCADE);
-- -----------------------------------------------------
-- Table `GamePlatform`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `GamePlatform` (
`id` INT NOT NULL AUTO_INCREMENT,
`GameID` BIGINT UNSIGNED NOT NULL,
`PlatformID` INT UNSIGNED NOT NULL,
INDEX (`GameID` ASC),
INDEX (`PlatformID` ASC),
PRIMARY KEY (`id`),
UNIQUE INDEX `GameID_UNIQUE` (`GameID` ASC, `PlatformID` ASC),
CONSTRAINT ``
FOREIGN KEY (`GameID`)
REFERENCES `Game` (`GameID`)
ON UPDATE CASCADE,
CONSTRAINT ``
FOREIGN KEY (`PlatformID`)
REFERENCES `Platform` (`PlatformID`)
ON UPDATE CASCADE);
Jay Ellsworth i took the approach of Nicolás Parada into account although i prefer a unique key pair for n:m releation with it's own auto_increment index .... it's easier to maintain them at least that's my opinion.
Wow you did a lot, thanks chilimatic!!
Ok I'm trying to be thorough, bear with me =)
I wasn't even clear on what "on update CASCADE" meant when you said that, so I googled it and did a little reading. I didn't realize that was a thing you had to specify, I assumed the constraints would take care of that sort of thing automatically. If I run
CREATE DATABASE gamelist DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
before I create my tables, do the tables inherit that character set and collation?
And your post reminded me that I forgot to put NOT NULL on all of my ID's, so thanks for that.
Ok, questions, starting from the top:
Why did you use CREATE TABLE IF NOT EXISTS on them? Wouldn't you get an error anyway if you tried to create a table that already exists?
And then how come you used NULL and then DEFAULT NULL on DeveloperID and NOT NULL on PublisherID? I don't want any of my ID's to be NULL do I? Just trying to understand. And I'm still wrestling with the primary key's on the two intermediary tables, should I use composite keys or give each their own ID column like I originally had? Uniqueness is achieved either way, but I guess I don't understand the performance consequences well enough to know what's best.
Also, what are the timestamps for? Is that something you've learned to do over time? Do you do that on every table?
On the tables that contain foreign keys you specified the Indexes, is that also common practice? Does any indexing get done automatically or do you always have to specify?
And then the constraints, why did you put
CONSTRAINT ``
Is the `` something to do with naming the constraint?
I dropped and recreated the tables as you wrote them and it didn't like the NULL's in the Primary Keys, but once I switched those to NOT NULL it worked perfectly in the first 4 tables. And it failed to create the Game table saying Cannot add foreign key constraint. I double checked for typo's and it looked perfect to me. I took out the `` from the CONSTRAINTs and it worked.
Once I got past those bits everything worked and now things are lit up on the foreign key tabs, what gives? Still don't understand why it didn't work when I did it. Is it because you typed out the constraints? The main difference I see is the ON UPDATE CASCADE part you added.
I guess I'm not going to complain, I just don't understand why yours worked and mine didn't. And I mean it really worked with yours, the constraints worked and the relationships are visible in the new EER diagram right off the bat.
Thank you so much for your help I appreciate it, now I'm just trying to absorb!
I wasn't even clear on what "on update CASCADE" meant when you said that, so I googled it and did a little reading. I didn't realize that was a thing you had to specify, I assumed the constraints would take care of that sort of thing automatically. If I run
dev.mysql.com/doc/refman/5.7/en/innodb-foreign-ke…
per default it just checks if an insert / update is possible -> does the entity with a certain ID which is referenced as a foreign key exist.
CREATE TABLE IF NOT EXISTS
this is so the script won't throw an error if a table might exists that's all.
`DeveloperID` INT UNSIGNED NULL DEFAULT NULL,
`PublisherID` INT UNSIGNED NULL DEFAULT NULL,
in theory we never want them to be null but what if a developer gets removed ? the next important question that is actually more important -> does actually only 1 developer exist ? usually games are developed by more than 1 developer so a n:m relation seams more appropriate.
the reason why I choose the nullable option is as follows -> the Game entry has a primary key so it should be able to exist on its own maybe sometimes you don't know the developer or the publisher -> shouldn't you still be able to save ? that's a design decision ofc.
There are usually two approaches -> start loose and tighten up or start tight and loosen up.
In my experience if it's not need for normalization purposes or standarizing data, starting a little more loose and tighten up later is less work than the other way around.
We could ofc argue about game names and unique key pairs -> for example can tomb raider exist only 1 time ? or should it be able to exist 10 times in different variations -> that's the standardizing part i mentioned or if tables are built associative -> which is basically a n:m relation with additional fields than we need normalization so that's why a -> you make a combined primary key as mentioned by Nicolás Parada or you add another primary key -> so it'S easier to update / delete entries because you just need 1 parameter.
But this is taste or based on design needs and you should always be aware of certain engine behaviours. For example an ENUM in MySQL is great for readability but every change will need an alter table operation which in MySQL is not transaction safe that's why I tend to use "option tables"
as an example to make it easier to understand:
CREATE TABLE IF NOT EXISTS `example_with_enum` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`optionSet` ENUM('a', 'b', 'c') NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
VS
CREATE TABLE IF NOT EXISTS `example_no_enums` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`option_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_example_no_enums_1_idx` (`option_id` ASC),
CONSTRAINT `fk_example_no_enums_1`
FOREIGN KEY (`option_id`)
REFERENCES `options` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `options` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
both of them are able to represent the same thing but in one case -> inserts will allow us to add as many options as we need
the enum solution on the other hand allows us to keep clearer "context" with in the table a classic example would be genders enum('m','f','o') or roles ('admin', 'blub') ...
but for a dynamic interface it's easier to query 10 options with a select than parsing an enum. -> still it depends on what SQL-Database System u use -> pgSQL for example has DDL within transaction so it's not that bad and ofc it's about the amount of entries -> if i got 1000 entries and alter table might be not that costly but if i got 100 000 000 entries it might not be the best idea.
Also, what are the timestamps for? Is that something you've learned to do over time? Do you do that on every table?
yes it's been a long journey but usually you want to know when something is changed or created.
`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
`modified` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
those timestamps just mean -> if an entry gets inserted and there is no Datetime set -> use your system_time and create.
as well as -> if something gets changed log the time.
in more complex systems you actually wanna log change. And here's the tricky part usually in bigger systems, based on experience, you add a "deleted timestamp" instead of deleting an entry and only after 2-3 months you actually have a cleanup job which removes them -> customers tend to do stupid things and we should be prepared.
And then the constraints, why did you put
CONSTRAINT ``
Is the `` something to do with naming the constraint?
well the ` in mysql means don't "interpret" the following string -> which is useful if you want for example name a column using a reserved word in MySQL.
the empty contraint was a hasty mistake of mine :) it's a mysql workbench "bug" if you don't cleanup manually.
is this explanation good enough or did i forget something ?: )
oh yes
CREATE DATABASE gamelist DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
if not defined otherwise the table inherit from the database -> i personally recommend to set the default character set in the my.cnf because srsly ... f*ck latin-1 ;D we're not in the 90s maybe it's still needed and valid in certain usecases for performance or memory reasons ... but hey ... let hit the limits before we do preoptimizations.