| Edit: Changed subject, was Music Col. new Make your post understandable by others -- MrProgrammer, forum moderator |
The same song interpreted by different artists never has the same length.
Code: Select all
CREATE TABLE SONGS(
N VARCHAR_IGNORECASE(100) NOT NULL,
A VARCHAR_IGNORECASE(100) NOT NULL,
T time not null,
id int identity
);Code: Select all
create table PL(
N varchar(20),
CD date default current_date,
PD date,
ID int identity
);Code: Select all
create table PL_S(
PLID int,
SID int,
ORD int,
primary key (PLID, SID),
foreign key (PLID) references PL (ID),
foreign key (PLID) references SONGS (ID)
);.Mr. Villeroy's comment: The same song interpreted by different artists never has the same length.
I would use a single song list with artist name and time.
Code: Select all
ALTER TABLE Playlist ADD Timing MINUTE_SECOND;Code: Select all
SELECT "Playlist"."ID_Playlist", "Playlist"."date", "Artist"."name", "Songs"."title", "Playlist"."time" FROM "Playlist", "Artist", "Songs" WHERE "Playlist"."ID_Artist" = "Artist"."ID_Artist" AND "Playlist"."ID_Song" = "Songs"."ID_Song"Of course it is possible to copy cell values from a spreadsheet into a database.RPG wrote: ↑Thu Nov 09, 2023 11:12 am As far I know does have OldMan63 a spreadsheet with 5000 records.
A new example short example you can find here: Ja, weer de muziek collectie
Maybe it is possible to use the spreadsheet for the database or copy the data in a new database.
I have later changed sheet "VB Table" a little: I have delete the empty column. It seems to me that the Column "Artist/Group" and "Country" are a kind of relations. I have the idea they can be used in forms; master/slave fields.Villeroy wrote: ↑Thu Nov 09, 2023 12:31 pm I see the spreadsheet "Map1.ods" with the first sheet having artists and titles as flat text with no relation to any other table. In this particular case, I would stick to that "main table" without splitting artists or titles into separate tables.
Store the titles and artist names as VARCHAR_IGNORECASE and add indices on these fields. This makes it easier and faster to search through the text.
Code: Select all
create table countries(
code char(3) not null primary key,
name varchar(50) not null
);
create table titles(
artist varchar_ignorecase(200) not null,
title varchar_ignorecase(200) not null,
t time not null,
y smallint not null,
cid char(3) not null,
id int identity,
foreign key (cid) references countries (code)
);
create table genres(
n varchar(30),
id int identity
);
create table g_t(
gid int, tid int,
primary key (gid, tid),
foreign key (tid) references titles (id),
foreign key (gid) references genres (id)
);