C#•4mo ago

✅ 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•4mo 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•4mo ago

ok sounds great. how do I do it?

Angius•4mo ago

Here's a tutorial I found: https://blog.devart.com/types-of-relationships-in-sql-server-database.html

Jimmacle•4mo ago

damn beat me to googling it i had the link in my clipboard

Angius•4mo ago

👉😎👉

Mekasu0124•4mo 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•4mo 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•4mo ago

what happens when 2 or more students have the same name?

Anu6is•4mo ago

a teacher ideally would have multiple students ...how does student name work then in your staff table?

Mekasu0124•4mo 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•4mo 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•4mo 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•4mo 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•4mo 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•4mo 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•4mo 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•4mo ago

They have a free course in relational database basics