To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works.
I have a database with 1m+ rows, currently organised as follows:
Rows: ID, FirstName, LastName, PetName, PetAge
I would like to split it as follows:
PersonID (PK), FirstName, LastName
PetID (PK), PetName, PetAge
RecordID (PK), MasterTable.ID, People.PersonID, Pets.PetID
PKs in all cases auto-increment so that more records can be added later.
The people and pets tables have been populated using:
INSERT INTO Pets(PetName, PetAge) SELECT PetName, PetAge From MasterTable WHERE 1 INSERT INTO People(FirstName, LastName) SELECT PetName, PetAge From MasterTable WHERE 1 INSERT INTO Records(ID) SELECT ID From MasterTable WHERE 1
So I have three tables. When I try to create the Records table, I can't get anything to work. I have tried:
INSERT INTO Records(PersonID, PetID, ID) SELECT People.PersonID, Pets.PetID, MasterTable.ID FROM MasterTable LEFT JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName LEFT JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge WHERE 1
I think the WHERE clause might be the problem. I have tried WHERE Pets.PetName = MasterTable.PetName and almost every kind of WHERE I can think of.
I have a few questions I'd really appreciate some help with as I'm going out of my mind here.
Does it matter the order of the LEFT JOIN clauses? Does it matter which table is specified first and which is specified last?
I initially tried INNER JOIN but I figure it's just going to join more columns than is necessary, is that right?
If I am inserting firstname and lastname, I can't match on firstname and lastname, right? As in, create the firstname lastname entries and then use that ID to match the next join?
It seems simple enough to split this into three, assign a PK to each, and then create a finale table where PKs relate to PKs, but apparently it's not.
When I add '''LIMIT 5''' the select returns the correct info. Without the limit clause, all my attempts have run for over 24h and not finished. Either they have been stuck copying everything to temp tables, or they have just said "selecting data" as the status.
Can someone please help?
Sorry if something doesn't make sense, I'll clarify as I go.