SQL Database Formation

[MannyKSoSo]

Hello all,

So I have a question about making a usable database using SQL and justme's Class_SQLiteDB.ahk
So to give an example of what my data looks like here is a small extract

Code: Select all

New Editions of ADMIRALTY Charts published 26 July 2018
2282
Black Sea, Plans in Romania.
A Mangalia to Midia.
B Portul Sulina.
C Portul Midia.
D Portul Mangalia.
Includes significant safety-related information as follows: changes to depths, buoyage, recommended track, anchorage area and coastline.
Note: On publication of this New Edition former Notice 3316(P)/18 is cancelled. This chart remains affected by Notices 2001(T)/18, 1959(T)/18 and 489(T)/18.
1:100,000
1:40,000
1:30,000
1:20,000
31
46
#
New Editions of ADMIRALTY Charts published 26 July 2018
2739
Ireland - West Coast, Brandon and Tralee Bays.
52° 13´·27 N. — 52° 26´·00 N., 9° 37´·44 W. — 10° 15´·31W.
Fenit Harbour.
52° 15´·52 N. — 52° 17´·13 N., 9° 51´·02 W. — 9° 54´·53W.
Includes changes to marine farms and shellfish beds. (The chart limits have been revised as the horizontal datum of this chart has been transferred to ETRS89 Datum).
1:37,500
1:12,500
4
22

Here the two separate pieces of information are broken up by a #.
In some cases certain pieces of information are linked together, for example: Black Sea, Plans in Romania. | 1:100,000
Another example: Fenit Harbour. | 1:12,500 | 52° 15´·52 N. — 52° 17´·13 N., 9° 51´·02 W. — 9° 54´·53W.
So each piece of information is separated by a character return, but when I make the database instead of creating a bunch of empty fields I would like to Link the fields together. Advice is welcome. Also the data can vary sum but not too much.

[nnnik]

Could you say which fields you want to store?
also I dont see a # in there anywhere?

[swagfag]

is this a question about parsing the text or creating the db schema?

Spoiler

i dont understand what u mean when u say u dont want "empty fields". Black Sea, Plans in Romania. isnt mapped to any scale, as u can tell. The other entry lacks a note. It seems to me empty fields are unavoidable

[MannyKSoSo]

So I have handled parsing the text, it's mainly a question about making the database. As for empty fields what I mean is that the fields would be chart number, international number (this can be null), main (which can contain scale and limits), plans (which includes all the plan titles, limits, and scales and this can be null if no plans exists), the includes and notes statement, folio, and catalogue page. The main reason I said I didn't want null fields is because I didn't want each line being it's own field and have a database that is capable of taking 12 plans when only 1 plan exists.
Also nnnik the # is above the line "New editions of".

[swagfag]

Code: Select all

PRAGMA foreign_keys = ON;

CREATE TABLE `Chart` (
	`chart_id`	INTEGER NOT NULL UNIQUE,
	`international_nr`	INTEGER,
	`title`	TEXT,
	`includes`	TEXT,
	`note`	TEXT,
	`folio`	INTEGER NOT NULL,
	PRIMARY KEY(`chart_id`)
);

CREATE TABLE `Plan` (
	`plan_id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	`chart_id`	INTEGER NOT NULL,
	`title`	TEXT NOT NULL,
	`coords`	TEXT,
	`scale`	TEXT NOT NULL,
	FOREIGN KEY(`chart_id`) REFERENCES `Chart`(`chart_id`)
);

CREATE TABLE `CataloguePage` (
	`page_id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	`chart_id`	INTEGER NOT NULL,
	`page_nr`	INTEGER NOT NULL,
	FOREIGN KEY(`chart_id`) REFERENCES `Chart`(`chart_id`)
);

[nnnik]

Which SQL database needs to be told that a primary key is unique?

[swagfag]

its indeed superfluous

[MannyKSoSo]

The chart table one is the who is unique, however it can occur where the same chary number occurs, if I understand sql well enough that means I can't use the chart number as the primary key correct? If that is true can I then use an increasing index number as the unique?

Edit: also considering the use of multiple tables I would guess a listview would not be the best suited so what display might be the best for this?

[MannyKSoSo]

Adjusting for the need of having a history I have come up with something like this

Code: Select all

PRAGMA foreign_keys = ON;

CREATE TABLE 'Chart' (
'Chart Number' INTEGER NOT NULL UNIQUE,
'International' INTEGER,
PRIMARY KEY('Chart Number')
);

CREATE TABLE 'Main' (
'Chart_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
'Chart Number' INTEGER NOT NULL,
'Title' TEXT,
'Scale' TEXT,
'Includes' TEXT,
'Note' TEXT,
FOREIGN KEY(`Chart Number`) REFERENCES `Chart`(`Chart Number`)
);

CREATE TABLE `Plan` (
`plan_id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`Chart Number`INTEGER NOT NULL,
`Title`	TEXT NOT NULL,
`Coords`	TEXT,
`Scale`	TEXT NOT NULL,
FOREIGN KEY(`Chart Number`) REFERENCES `Chart`(`Chart Number`)
);

CREATE TABLE `CataloguePage` (
'page_id'	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
'Chart Number'INTEGER NOT NULL,
'Folio' TEXT,
'Catalogue Page' TEXT,
FOREIGN KEY(`Chart Number`) REFERENCES `Chart`(`Chart Number`)
);

Hopefully I have the right idea for that.
Also since SQL creates the search statement as info|info|info is there a better way to display the information than a list view as I would like to view the data in a manner similar to this

Code: Select all

Chart Number	Title					Scale		Limits	....
2282			Batlic Sea				-			-
				A Mangalia to Midia.	1:100,000	-
				B Portul Sulina.		1:40,000

If this isn't possible I can deal with the list view.

Edit: Missed a comma