CODEWITHSUNDEEP
sqlmysqldatabase-designdata-modeling
Sinan
Sinan

Reputation: 5980

how relate one table to another for future records

I have a games table which holds the data about a game. Then another table which holds the data about news.

So far so good.

First I thought about creating a junction table for game_news so I could relate news to games.

This way works as intended when the game exists. So whenever I insert a news I can relate it to a game using the junction table.

However there are cases when there is news about game but the game isn't published and it doesn't exists.

So my question would be; is there a way to relate these news to a particular game when the game record is created.

What is the best way to do this? Any ideas?

Upvotes: 2

Views: 273

Answers (5)

KM.
KM.

Reputation: 103587

The junction table is the way to go. If a news article is about more than one game, then you need it. To handle games that do not exist yet, just insert a row for them, include all the info you currently know about it (possibly from the news article) and have a status column that marks it as not released yet. You can display this game as not released yet or rumor, etc.

set the tables up something like this:

Games
GameID           int          not null auto increment PK
GameStatus       char(1)      not null "P"=published, "N"=not released yet, "R"=game is only a rumor
GameReleaseDate  date         null
GameName         varchar(...) not null
GameDescription...
...

News
NewsID      int               not null auto increment PK
NewsTitle   varchar(...)      not null
...

GameNews
GameNewsID  int auto increment PK
GameID      int FK to Games.GameID
NewsID      int FK to News.NewsID

With this setup you can have multiple games related to a single News item. Just insert all the proper GameNews rows to link each game to the News row.

If a game has not been published yet, you can still link it to news by creating the Games row with the status "N" or "R" (or something like that) and using the GameNews table just as you would for a published game. You could populate all the fields within Games with as much info as possible and update it as you find out more. in the end you would have complete game info in the Games row (after the game is published) and it would link to the all the News rows, even when it was just a rumor in the news.

To give you an idea about what I'm talking about, here is a sample of what the data for a "rumored" game would look like over time (this is a simplified example and without multiple Games per News rows):

data as of 1/1/2010
    Games    GameID  GameStatus  GameReleaseDate  GameName
             1234    "R"         1/1/2012         "God of War 4"
    News     NewsID  NewsTitle
             543     "Future Of Games"
    GameNews GameNewsID  GameID  NewsID
             768         1234    543

data as of 4/1/2010
    Games    GameID  GameStatus  GameReleaseDate  GameName
             1234    "R"         1/1/2012         "God of War 4"
    News     NewsID  NewsTitle
             543     "Future Of Games"
             544     "Interview with John Hight"
    GameNews GameNewsID  GameID  NewsID
             768         1234    543
             769         1234    544

data as of 11/20/2010
    Games    GameID  GameStatus  GameReleaseDate  GameName
             1234    "N"         12/31/2011         "God of War IV"
    News     NewsID  NewsTitle
             543     "Future Of Games"
             544     "Interview with John Hight"
             545     "God of War Expected Next Year"
    GameNews GameNewsID  GameID  NewsID
             768         1234    543
             769         1234    544
             770         1234    545

data as of 8/15/2011
    Games    GameID  GameStatus  GameReleaseDate  GameName
             1234    "N"         12/01/2011         "God of War IV"
    News     NewsID  NewsTitle
             543     "Future Of Games"
             544     "Interview with John Hight"
             545     "God of War Expected Next Year"
             546     "Retailers Get Ready For New Games"
    GameNews GameNewsID  GameID  NewsID
             768         1234    543
             769         1234    544
             770         1234    545
             771         1234    546

data as of 1/1/2012
    Games    GameID  GameStatus  GameReleaseDate  GameName
             1234    "P"         12/01/2011         "God of War IV"
    News     NewsID  NewsTitle
             543     "Future Of Games"
             544     "Interview with John Hight"
             545     "God of War Expected Next Year"
             546     "Retailers Get Ready For New Games"
             547     "God of War IV Review"
    GameNews GameNewsID  GameID  NewsID
             768         1234    543
             769         1234    544
             770         1234    545
             771         1234    546
             772         1234    547

if on 1/1/2012 you were to look at News.NewsID=543 you would see that it links to the complete and reviewed Games.GameID=1234, even though the News.NewsID=543 article is about a "rumored" upcoming version of God of War. And all this was done without making any changes to the old News or GameNews rows.

Upvotes: 2

Erwin Smout
Erwin Smout

Reputation: 18408

"However there are cases when there is news about game but the game isn't published and it doesn't exists."

You should get your thoughts straightened out on the meaning of "existance". If something does not exist, then there is nothing that can relate to it, period.

If your business reality encompasses a difference between "existing and being known to the public" and "existing, but only privately, and not being known to the public", then your models should acknowledge and refelct that reality.

It is perfectly possible to relate information to any thing that "exists, but not publicly". It is not possible to relate information to any thing that does not exist.

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

Putting two foreign keys in a junction table is a good idea. Foreign keys reference things that exist. Enforcing this is called "referential integrity". Permitting references to non existent items is the road to chaos.

If you get a news article about a game that does not yet exist (in the database), you basically have two choices: add the game to the game table before adding the reference to the game in the junction table; alternatively, omit the relationship between the article and the game, for the time being.

Upvotes: 1

chris
chris

Reputation: 9993

you have 3 options - 1 is to set a flag on the 'game' table to say whether it's released or not, and show only the name (or not even that) if the game is unreleased.

another is to edit the news item after you add the game item and link it then. because you cannot tell what a unique id is going to be before you add a game.

a 3rd UNRECOMMENDED option is to link them via game name instead of a primary key, so you have a column called game_name in the news table and it links the tables that way. however, that will fail if you mis-spell things and is nowhere near as good as options 1 or 2.

Upvotes: 2

dkamins
dkamins

Reputation: 21918

The simplest answer is to have a foreign key from news to game. If you are creating news for a game that doesn't exist yet, just create a stub record in your games table. You can flag it as such if you want. When you add the game, just flesh out this stub record.

Upvotes: 1

Related Questions