CODEWITHSUNDEEP
mysqldatabase
Tim Marshall
Tim Marshall

Reputation: 360

Correctly establishing my database tables

My Initial Flawed Logic

Anyone who is somewhat familiar with establishing databases, creating tables and whatnot will instantly be able to see my initial logic of database establishment is completely flawed and bad practice. A somewhat snippet is below;

After somewhat researching...

I'm now under an understanding that a database file root like structure is not the way forward and instead have multiple tables under the root so to speak like so;

Resulting SQL Query

CREATE TABLE User_Interests
(
        UserID int NOT NULL,
        MovieInterests TEXT,
        TVShowInterests TEXT,
        BookInterests TEXT,
        MusicInterests TEXT,
        PRIMARY KEY (UserID)
);
CREATE TABLE Game_Developers
(
        GameDevID int NOT NULL,
        Name varchar(255),
        Website varchar(255),
        PRIMARY KEY (GameDevID)
);
CREATE TABLE Game_Manufacturers
(
        GameManufactID int NOT NULL,
        Name varchar(255),
        Website varchar(255),
        PRIMARY KEY (GameManufactID)
);
CREATE TABLE Game_Publishers
(
        GamePublisherID int NOT NULL,
        Name varchar(255),
        Website varchar(255),
        PRIMARY KEY (GamePublisherID)
);

The Specifics - QUESTIONS

  1. How do I achieve a database like so
    • Okay so I've added what I've attempted / researched and whatnot, however I do not know whether or not this is in fact the correct way forward?
  2. Am I using the correct identifiers?
    • When I use the word 'identifiers' what I mean by this is the likes of; int NOT NULL and varchar(255).

Upvotes: 0

Views: 64

Answers (2)

avk
avk

Reputation: 871

This might get you started:

Product Type: This could be Game, Movie, Book or Music

create table Product_Type( id INT, description VARCHAR( 1024 ), ... )

Product Sub Type: This could be Console Game

create table Product_Sub_Type( id INT, description VARCHAR( 1024 ), product_type_id, ... )

Relation_Type This would be the developer or manufacturer of the product

create table Relation_Type( id INT, description VARCHAR( 1024 ), ... )

Relation: This is a prototype for a relationship like Developer, Manufacturer, etc.

create table Relation( id INT, description VARCHAR( 1024 ), relation_type_id INT, ... )

Relation_Info_type: Info types like Website, Name, Address

create table Relation_Info_Type( id INT, description VARCHAR( 1024 ), ... )

Relation_Info: This can be Name, Address or Website. (in case the relation has more than one address or website)

create table Relation_Info( id INT, relation_id INT, relation_info_type_id INT, description VARCHAR( 1024 ), ... )

Product_Relation: This would be the many-to-many way of linking relations to products, e.g. the developer and the manufacturer.

create table product_relation( id INT, relation_id INT, product_id INT, relation_info_type_id INT, description VARCHAR( 1024 ), ... )

To keep it simple (and flexible) you can do the same for Genre ( retro, action, adventure, puzzle ): followed by many-to-many tables like 

create table genre( id INT, description VARCHAR( 1024 ), ... )
create product_genre( id INT, product_id INT, genre_id INT, ... )

And for platforms ( Windows, IOS, Ubuntu, Android ):

create table platform( id INT, description VARCHAR( 1024 ), overview VARCHAR( 4096 ), developer_id, manufacturer_id,... )
create table platform_product( id INT, platform_id, product_id, ... )

These are products like the move Interstellar or the game Final Fantasy VII

create table Products ( id INT, description VARCHAR( 1024 ), product_sub_type_id INT, ... )

All [name]_id columns should be read as having a foreign key relation with table [name], except for developer_id and manufacturer_id which refer to table Relation.

Upvotes: 1

jpw
jpw

Reputation: 44931

This isn't a proper answer but turned out too long for a comment...

Without knowing your business requirements and constraints it's kind of hard to say much about the validity of the model, but remember one of the goals of the relational model is to avoid duplication of data, so think about how data is stored for a company that is both a developer. manufacturer and published of console games, while it also published books that tie into some game franchise and the soundtracks for the games they've developed. (And maybe they do PC/Mac games too...).

The use of identifiers looks ok - it's largely a matter of style if you chose to identify a game developer as Game_Publishers.GamePublisherID or simply Game_Publishers.ID. Shouljdn't the identifying name attribute be not null though?

The User_Interests table looks like it could use some remodeling, but again, it's unclear what your needs are - however a column named MovieInterests suggests that you intend to store more than one value in it - maybe an array of interests, and if so this would break the normal form and lead to a mess and much regret later.

Upvotes: 0

Related Questions