Play with simple dummy databases having a few one-to-many relations and some many-to-many relation.
The best tool to start with is a sheet of paper where you sketch lists of columns representing the tables and how they should be related to each other (something like the relations window).
In Base, I always start with a blank database where each column has the right type and where relations are not set in the beginning. An empty database structure is much easier to modify than one with test data because the data can be contradictive to new rules. Before adding new columns to a table, it is best practice to remove all test data, modify the structure and fill in new test data into the new structure.
If you are confident that your tables and columns are suitable to take all the data, remove dummy data and establish relations. Relations enforce referencial integrity (reject title IDs in a playlist where there is no such song title ID in the list of songs). Relations also improve lookup performance. While changing the database structure and while working with dummy data, relations can be in your way.
Integer Auto-IDs are the correct primary keys for the vast majority of lists. Base never creates auto-IDs when you create new tables by copy&paste. This is why I always create a new empty table first (with auto-ID) and then fill in any data. Junction tables should have a compound primary key if you want to avoid duplicate assignments (each article occurs only once on the same invoice).
How to fill in test data? Calc is my favourite tool to generate random numbers, dates, times, booleans and dummy strings for testing. A Calc range copied into an existing database table always gives proper results IF (and only if) the rules of your database structure are fulfilled by the pasted data.
How to fill existing “real world” data from Calc into a blank database structure? In order to avoid failing or incomplete import, do the following tests in the spreadsheet and edit your data on sheet until they fulfill all the rules of your database structure:
=MAX(LEN(column)) [entered with Ctrl+Shift+Enter as array formula] returns the max. length of a text column for the right size of a VARCHAR field.
=COUNTBLANK(column) counts blanks. There should be no blanks in a mandatory column.
=COUNTA(column) counts any values text or number.
=COUNT(column) counts numbers
=COUNTA(column)=COUNT(column) TRUE if there are only numbers.
=ROWS(column)=COUNT(column) TRUE if there are only numbers with no blanks.
=MIN/MAX(column) min and max values of a column to determine the right numeric type.
=N(MATCH(value;column;0)) returns 0 if value does not occur in some other table’s column (test for referencial integrity).
“column” should be a reference like A$2:A$1001 if you have column labels in row #1 and 1000 records below the labels.
=COUNTIF(value;same_column)>1 returns TRUE if there are duplicates in the same column.
Date/Time numbers need to be formatted accordingly. ISO date/times are allowed to be text. Text value “2021-09-17 23:45:59” fits properly into a database column of type TIMESTAMP, but not text value “9/7/2021 11:45 am”
Column types should be as small as possible and as large as necessary.
