How would I format this-CodePudding

I just started learning SQL Three weeks ago for College. I am trying to practice making databases. I ran into an issue that I can't really understand how to fix. I found something that was close to my question but I could not really understand what they were trying to do.

I have a database with PLanets and moons, my issue is some planets have many moons. From what I understand on normalization is you can't have duplicate info in one cell?

Here is what I wrote, but if I run it this way I would have multiple moonid in my planet table.

CREATE TABLE `planet`( 
    `plid` int NOT NULL,
    `plname` varchar(50) DEFAULT NULL,
    `plsize` int DEFAULT NULL,
    `moonid` int DEFAULT NULL,
    `pltype` varchar(50),
    `plage` int DEFAULT NULL,
    PRIMARY KEY (`plid`)
)   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `moon`(
    `moonid` int NOT NULL,
    `moonname` varchar(50) DEFAULT NULL,
    `moonsize` int DEFAULT NULL,
    `moonage`int DEFAULT NULL,
    `plid` int DEFAULT NULL,
    PRIMARY KEY (`moonid`)

This is what was suggested but I don't really see how this would fix my issue.

CREATE TABLE `moonplanet`(
    `moonid` int NOT NULL,
    `plid` int DEFAULT NULL,
    PRIMARY KEY (`moonid`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CodePudding user response：

using relationship - one to many table structure looks like this....just remove moonid from planet table....using JOIN u can able to fetch multiple moon values

CREATE TABLE `planet`( 
    `plid` int NOT NULL,
    `plname` varchar(50) DEFAULT NULL,
    `plsize` int DEFAULT NULL,
    `pltype` varchar(50),
    `plage` int DEFAULT NULL,
    PRIMARY KEY (`plid`)
)   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `moon`(
    `moonid` int NOT NULL,
    `moonname` varchar(50) DEFAULT NULL,
    `moonsize` int DEFAULT NULL,
    `moonage`int DEFAULT NULL,
    `plid` int DEFAULT NULL,
    PRIMARY KEY (`moonid`)

CodePudding user response：

AFAIK a moon can not be shared by two or more planets, so this is a 1:M relationship. You needn't a separate moonplanet table for the realationship.

We model a 1:M relationship with a column of Moon referencing the Planet and your table has already got it.

Yes, a column with multiple values in one cell breaks 1NF. So just drop the moonid column from the planet table.