First DB - How to structure required information

Question

I watched a few youtube videos about how to structure a database using tables and fields. I am a bit confused about how to strucuture my information.

I have put my attempt below:

// Identifier Table
// This is where we give each item a new unique identifier
UniqueID            []

// Item Table
// This is where the main content goes which is displayed
UniqueID            []
Title               []
Description         []
Date                []
Location            []
Coordinates         []
Source              []
Link                []

// Misc Table
// This is additional useful information, but not displayed
geocoded            []
country name        []

By separating out the uniqueID when I delete a record I can make sure that new records still have a unique incrementing ID. Can I get some feedback on how I divided up my data into three tables.

Answer 1

you gave us no hint what you want to represent in your db.

For example: if location and coordinate describe a building or maybe room, than it could be useful to save that information in an extra table and have a relationship from item to it, as this would allow to easily fetch all items connected with on place.

Of course you should apply the same principle for country: a locations lays with-in a country.

BEGIN;
CREATE TABLE "country" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL
)
;
CREATE TABLE "location" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(255) NOT NULL,
    "coordinate" varchar(255) NOT NULL,
    "country_id" integer NOT NULL REFERENCES "country" ("id")
)
;
CREATE TABLE "item" (
    "id" integer NOT NULL PRIMARY KEY,
    "title" varchar(25) NOT NULL,
    "description" text NOT NULL,
    "date" datetime NOT NULL,
    "source" varchar(255) NOT NULL,
    "link" varchar(255) NOT NULL,
    "location_id" integer NOT NULL REFERENCES "location" ("id")
)
;

Answer 2

In the case stated above I would pack everything into one table since there is not enugh complexity to benfit from spliting the data into diferent tables.

When you have more metadata you can split it up into:

Item (For display data)

ItemMeta (For meta data)