4

This seems like a basic question to me but I'm having trouble finding practical solutions.

I have two tables, one for video game consoles, and another for the games themselves.

Consoles
+ -- + --------- +
| ID | Name      |
+ -- + --------- +
| 0  | Console X |
+ -- + --------- +
| 1  | Console Y |
+ -- + --------- +

Games
+ -- + ------ + ------- +
| ID | Name   | Console |
+ -- + ------ + ------- +
| 0  | Game X | ?       |
+ -- + ------ + ------- +
| 1  | Game Y | ?       |
+ -- + ------ + ------- +
| 2  | Game Z | ?       |
+ -- + ------ + ------- +

My question is how do I do the data entry from scratch for the Game.Console column here? As a human, it'd be easy to write the Console.Name there, but obviously that's not recommended to use as a Foreign Key ID. But if I use the integer Console.ID as the Foreign Key, for 700 games and 30 consoles, it seems like I'd have to manually cross-check the table as I enter in all those numbers.

How do I do the task of data entry for an association like this? Is there a better solution than manually entering numbers? I'd think if I was using an IDE there might be some feature for a drop-down of foreign rows to fill in a foreign key value, but I haven't found anything.

Additional Info: I'm not expecting user input in this application, just my own personal data, so I'm not worried about validation, just how to alleviate tedium in the initial data entry. Entering cross-referenced integers seems like the job for a computer or tool.

  • I'm using MySQL and MySQL Workbench, but I think my question is kind of independent of that. A MySQL Workbench solution would be useful, but any sort of tool would be helpful. – MattTreichel Feb 11 '17 at 18:52
  • 2
    How to design data storage and how to organise data entry based on that design are two distinct, even if related, problems. "I have tables A and B and I want a way to associate rows from A with rows from B in a many-to-many fashion. What is the best way to do that?" That's all there is to the design side of the problem. Asking about data entry should be separate from that and, most likely, on a different site. – Andriy M Feb 11 '17 at 19:08
  • @AndriyM Where should I be asking then...? – MattTreichel Feb 11 '17 at 19:18
  • I'm not sure. I would think SO, since in any event you'll probably want to enter data as names while storing the associations as references, which already sounds as though it would need some scripting/programming. But that's just my reasoning and, as I said, I'm not really sure. – Andriy M Feb 11 '17 at 20:18
  • With other DBMS you could create a view that joins both tables together then you could insert into that view and the an INSTEAD OF trigger would take care of doing the lookup. With MySQL you probably need a stored procedure that does the insert – a_horse_with_no_name Feb 13 '17 at 17:38
3

I think the easiest solution for you would be to create a stored procedure that took @GameName and @ConsoleName.

CREATE PROCEDURE InsertGame @GameName VARCHAR(50), @ConsoleName VARCHAR(50)
AS
BEGIN
DECLARE @ConsoleID INT

SELECT @ConsoleID = ConsoleID FROM Console WHERE ConsoleName = @ConsoleName

IF @ConsoleID IS NULL
BEGIN
INSERT Console (Name) SELECT @ConsoleName
SELECT @ConsoleID = scope_identity()
END

INSERT Game (Name, Console) SELECT @GameName, @ConsoleID

END

If you entered in a @ConsoleName that didn't already exist in the Console table, it would INSERT it there. Next the stored procedure would look up the ID for the console name you provided it, and INSERT a record into the Game table with @GameName and the @ConsoleID.

Then you'd call that stored procedure for each Game you were planning to enter:

EXEC InsertGame 'Grand Theft Auto 5', 'PS4'
EXEC InsertGame 'Grand Theft Auto 5', 'PC'
EXEC InsertGame 'Final Fantasy XV', 'PS4'
EXEC InsertGame 'Overwatch', 'PC'
EXEC InsertGame 'Civilization 5', 'PC'

And so on until you've inserted all of your games.

Please note that the exact syntax I've used is for the database platform I am most familiar with, but the underlying logic should be usable in any database platform.

Also, as I write this I am struck by the idea that what you really want is to support a many-to-many relationship between game titles and platforms, since a platform can have many games and a game can be available on multiple platforms. That would take a little more work, but would be helpful for efficient storage and would give you the ability to easily determine which platform(s) a given game was available for without doing a text-match.

So, at a high level you'd need another table. The existing Game table would not have a ConsoleID column. Instead there would be a new table, likely called Console_Game, that would include only ConsoleID and GameID as keys. Then the stored procedure would need to check for both the existence of ConsoleName, and the existence of GameName. Inserting into both tables as necessary, and then finally write a record to Console_Game to show that a given game is available on a given console.

This will be more work upfront, but it is a more scalable solution than what you currently have. If this is for a job and not a hobby you should probably go that route.

  • 1
    They don't have to be unique, the stored procedure will figure out is a console name has been used before and insert it if it doesn't or reuse it if it does. And yes, this is SQL Server syntax, but you can utilize the same logic in other DBs. – Matthew Sontum Feb 11 '17 at 18:35
  • 1
    But if all 30 of my consoles were already in the database and named 'PC', I'm not sure how this script differentiates them? – MattTreichel Feb 11 '17 at 18:42
  • 1
    There should only be one console named PC, based on the rules for database normalization. Pick one, assign that ID to all existing entries in the Game table for PC, then delete all PC entries in the Console table that don't use that number. Otherwise the SP will just pick one of the existing PC entries arbitrarily, likely the first or last one. – Matthew Sontum Feb 11 '17 at 18:52
  • 1
    But then why not just use the name as an ID? And if instead it was 30 people named "John", what do I do? – MattTreichel Feb 11 '17 at 19:26
  • 1
    It's better to use an ID. It uses less space when saving the foreign key and in the future you could save more information about a console than just the name. For example, you could add a column for when the console was released. – Matthew Sontum Feb 11 '17 at 22:19
1

Your concern about using Name or ID is understandable. It is a well known topic in design. The Name is termed a natural key since it appears naturally in the data. The ID is a surrogate key as it is used instead of a natural key.

There are several questions on this topic. (Ignore the first one about postgres array.)

  • I understand the difference of the ID and the name, and using an ID is recommended practice, but my question is about how I would implement that practice. ie. What tools would help to do the data entry of foreign keys that are ugly values like integers. – MattTreichel Feb 13 '17 at 19:34

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.