I’m building a database from spreadsheets. I want to breakout some of the columns into related tables. Phone numbers for example so that a record can have multiple phone numbers. Not sure how best to do that since I’ll need the primary key of the parent table in the child table. I’m thinking I’ll import the spreadsheets into the db so that each record gets a primary key. Then I’ll export the data columns for the new table with the primary key of the parent. Then I’ll import that into a new table and thus have the relationship.
I would make a spreadsheet for each table, filling the datas with the original spreadsheet
and filling a column with the primarykey. it’s easier with a spreadsheet
then create the tables, with the columns equal to the columns in the spreadsheet
then import the data each spreadsheet in each table
the relations have to be made in the spreadsheet, or you will have to fill them by hand.
also possible, a big method to import all at once in the tables.
There’s also a lot or records in the whole project. Maybe 300,000 in a number of separate spreadsheets. Basically, it’s a database of doctors broken down by their specialty. Each specialty is a separate spreadsheet. Allergist, pulmonologists, etc. Each table looks something like this for example: Name, Address, Phone. Just using that as an example. The doctor is the unique entity to which all data relates. There is no primary key at this point. The phone is there and I don’t want to lose its association to the doctor.
Thanks for the suggestions. I’ll experiment with a subset of the data and see how it goes.
then it can go to a one table database ?
the common fields : name address phone
and add a column specialty and a row id (autoincrement)
then import one spreadsheet at a time into the db, specifying the specialty at each import.
What you have to do depends on your data. Develop a new entity relationship model first. Then do your import row by row and write the data into the new tables.
Something like the following:
Create a row in the main table for the doctor. Get the rec id.
Write the phone numbers into the phone number table including the foreign key from the doctor table.
And so on.
A long time ago I had to do something similar with a Lotus Notes “database”. That was so painful.
I just did something similar in order to create some sample data. The fake data I used had Company, First Name, Last Name, Phone, Email, Web, and fields for a Postal address.
I added a column for the ContactKey and columns for PhoneLabel, EmailLabel, WebLabel, and PostalLabel.
Exported Contacts.txt with the ContactKey, Company, First Name, Last Name.
Exported Phone.txt with ContactKey, PhoneLabel, Phone
Exported Email.txt with ContactKey, Email Label, Email
Exported Web.txt with ContactKey, Web Label, Web
Exported Postal.txt with ContactKey, Postal Label, Postal
Imported Contacts.txt into the Contacts table of the database.
Imported Phone.txt into the ContactsComms table of the database.
Imported Emai.txtl into the ContactsComms table of the database.
Imported Web.txt into the ContactsComms table of the database.
Imported Postal.txt into the ContactsComms table of the database.
In the end, I had two tables with all the related data.
You don’t need a primary key in the input data. The database will provide it for you. And you don’t need to do it in two passes, either. Given a spreadsheet with columns for Name, Address, and Phone, read each record and insert into the two tables.
dim db as New SqliteDatabase
// open the database
dim Name, Address, Phone as String
// read a row from the spreadsheet into the variables
dim rec as New DatabaseRecord
rec.Column("Name") = Name
rec.Column("Address") = Address
db.InsertRecord("NameTable", rec)
dim PrimaryKey as Integer = db.LastRowID
rec = New DatabaseRecord
rec.Column("Phone") = Phone
rec.Column("NameTableID") = PrimaryKey
db.InsertRecord("PhoneTable", rec)
