C
C#•10mo ago
Mekasu0124

✅ Common Keys in Database Tables

cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [students] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [Birthday] VARCHAR(2048),
                    [ParentEmail] VARCHAR(2048),
                    [ParentHomePhone] VARCHAR(2048),
                    [ParentHomePhone2] VARCHAR(2048),
                    [ParentCellPhone] VARCHAR(2048),
                    [ParentCellPhone2] VARHCAR(2048),
                    [StudentAddress] VARCHAR(2048)
                    )";

cmd2.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [staff] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [StudentName] VARCHAR(2048),
                    [NumberOfStudents] INTEGER,
                    )";

cmd3.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [parents] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [StudentName] VARCHAR(2048)
                    )";
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [students] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [Birthday] VARCHAR(2048),
                    [ParentEmail] VARCHAR(2048),
                    [ParentHomePhone] VARCHAR(2048),
                    [ParentHomePhone2] VARCHAR(2048),
                    [ParentCellPhone] VARCHAR(2048),
                    [ParentCellPhone2] VARHCAR(2048),
                    [StudentAddress] VARCHAR(2048)
                    )";

cmd2.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [staff] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [StudentName] VARCHAR(2048),
                    [NumberOfStudents] INTEGER,
                    )";

cmd3.CommandText = @"CREATE TABLE IF NOT EXISTS
                    [parents] (
                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    [FirstName] VARCHAR(2048),
                    [MiddleName] VARCHAR(2048),
                    [LastName] VARCHAR(2048),
                    [AlternateFirstName] VARCHAR(2048),
                    [AlternateLastName] VARCHAR(2048),
                    [PhysicalAddress] VARCHAR(2048),
                    [City] VARCHAR(2048),
                    [State] VARCHAR(2048),
                    [Zip] INTEGER,
                    [County] VARCHAR(2048),
                    [Country] VARCHAR(2048),
                    [PrimaryNumber] VARCHAR(2048),
                    [SecondaryNumber] VARCHAR(2048),
                    [PrimaryEmail] VARCHAR(2048),
                    [SecondaryEmail] VARCHAR(2048),
                    [StudentName] VARCHAR(2048)
                    )";
I'm writing my database logic for creating my relative database. I'm trying to find a good way to link these three tables together through a common variable. For example: The principle receives a new student and they get enrolled. The students information will go into the students table, the students parents information will go in the parents table, and the student will get assigned to a teacher, or number of teachers, and the teachers information already exists for when they were added to the gradebook on hire. So if a teacher needs to see parent/student information or the principle needs to see what teacher is assigned to the student, what's a good way to link these three tables together so that I can just do one database call and have access to the all the information from all 3 tables?
24 Replies
Angius
Angius•10mo ago
Seems like it's time to learn about the "relational" part of "relational database" Instead of having ParentEmail and all those other Parent properties in the Student, the Student should have a foreign key to Parent That way, you can use a JOIN to get student info and related parent info
Mekasu0124
Mekasu0124OP•10mo ago
ok sounds great. how do I do it?
Angius
Angius•10mo ago
Jimmacle
Jimmacle•10mo ago
damn beat me to googling it i had the link in my clipboard
Angius
Angius•10mo ago
👉😎👉
Mekasu0124
Mekasu0124OP•10mo ago
yea lol I'm lost I was gonna use StudentName as the item that was shared between all 3 tables. So like student will have 1 name. Parent will be two individual entries for two separate kids and then teacher will have a list of students that I'll just add each new student's names to so that when I pull information, I can query all three databases and whatever. idk how to explain it or do it lol
Angius
Angius•10mo ago
You can want whatever you want Relational databases won't conform to your wants There's no "list", there's no "shared" There are only foreign keys
Jimmacle
Jimmacle•10mo ago
what happens when 2 or more students have the same name?
Anu6is
Anu6is•10mo ago
a teacher ideally would have multiple students ...how does student name work then in your staff table?
Mekasu0124
Mekasu0124OP•10mo ago
there will be a pop up window that shows the list of students with the same first and last name with some small detailed information like addresses and parents names that will display and the teacher selects the student that want
Angius
Angius•10mo ago
CREATE TABLE Parents (
    INT Id PRIMARY KEY AUTO INCREMENT,
    VARCHAR(50) Name NOT NULL
)
CREATE TABLE Students (
    INT Id PRIMARY KEY AUTO INCREMENT,
    VARCHAR(50) Name NOT NULL,
    INT ParentOne FOREIGN KEY REFERENCES Parents(Id),
    INT ParentTwo FOREIGN KEY REFERENCES Parents(Id)
)
CREATE TABLE Parents (
    INT Id PRIMARY KEY AUTO INCREMENT,
    VARCHAR(50) Name NOT NULL
)
CREATE TABLE Students (
    INT Id PRIMARY KEY AUTO INCREMENT,
    VARCHAR(50) Name NOT NULL,
    INT ParentOne FOREIGN KEY REFERENCES Parents(Id),
    INT ParentTwo FOREIGN KEY REFERENCES Parents(Id)
)
Here's some pseudo-SQL
Jimmacle
Jimmacle•10mo ago
no i mean in terms of your program's logic how would it know how to correlate the entires if there are duplicate names and the names are what you're trying to use to link things together?
Angius
Angius•10mo ago
Yeah If there's Bob John in grade II and Bob John in grade III And parent, Marie John has a son, Bob John Which one is it? That's why you need IDs, primary keys that are unique So that grade II Bob John has ID 69, grade III Bob John has ID 420, and Marie John has a sone with ID 420 We now know which one it is And foreign keys let us enforce it, so that we cannot assign Marie John a kid with ID 1234, if such a kid does not exist
Mekasu0124
Mekasu0124OP•10mo ago
I was originally going to use the students ID as each tables id for each entry. That way when the student was selected, I could use that to get the information, but I realized that I couldn't have multiple entries of the same teacher for each student in the teachers table, so then I thought about writing in the parents information and the teachers information and storing their names in the students table with the student. I honestly don't know. I've never tried to make a gradebook before. but tbh, it's a gradebook. It only needs to house enough information about the student to just save their names and grades
Angius
Angius•10mo ago
Tell you what, forget C# for a moment Start by learning SQL Because it seems that's where your problem lies No, not even SQL, just the relational database model And how to model data Download MySQL Workbench or something, it lets you create a database graphically Connect tables together with lines and all And generate code from that
Angius
Angius•10mo ago
Harvard, as always comes in clutch: https://www.harvardonline.harvard.edu/course/cs50s-introduction-databases-sql
Harvard Online
CS50's Introduction to Databases with SQL
An introduction to databases using a language called SQL in an online course from Harvard.
Angius
Angius•10mo ago
They have a free course in relational database basics
leowest
leowest•10mo ago
that one looks like a paid one thou
Jimmacle
Jimmacle•10mo ago
might only cost money for the certificate afaik cs50 is free
Angius
Angius•10mo ago
Yeah, the course is free
leowest
leowest•10mo ago
ah yeah after enrolling I can access it
Angius
Angius•10mo ago
You only need to pay if you want the certificate
leowest
leowest•10mo ago
it was a bit of a hassle getting there
Mekasu0124
Mekasu0124OP•10mo ago
ok thanks
Want results from more Discord servers?
Add your server