Database Design Tutorial

Поділитися
Вставка
  • Опубліковано 23 гру 2024

КОМЕНТАРІ • 128

  • @angelamalik9437
    @angelamalik9437 7 років тому +20

    I cannot fault anything about this video. It's simple, and you've presented the information in an elegant, clear manner at a steady but not-too-slow pace, and the information is so incredibly useful. That bridge table idea is exactly what I was needing to solve my DB design. Thank you for sharing your knowledge!

  • @keithrusso8681
    @keithrusso8681 6 років тому +28

    Thank you for taking the time to create this tutorial. Sharing your apparent years of accrued knowledge has saved myself and others countless hours of frustration. Because of your tutorial, I'm not even thinking about smashing the wife's favorite coffee mug anymore. Thank you again, sir.

    • @mrbranmar
      @mrbranmar 4 роки тому

      @Lh Lh no he's 12 and a half

  • @Ckrispbacon
    @Ckrispbacon 4 роки тому +25

    I have been struggling for two weeks trying to understand many-to-many and this example made it all clear now. You're the best!

  • @_rahiali
    @_rahiali 5 років тому +7

    no one has ever made this any simpler than this video. thanks very detailed, great examples, and thank you

  • @dylannickelson3815
    @dylannickelson3815 4 місяці тому

    This video was so enjoyable to watch. Your teaching style and pace is really effective. You explain poor database design so well.

  • @Eag757
    @Eag757 Рік тому

    Thank you for presenting such explanation that isn't provided on my online studies.

  • @sparshkhurana5390
    @sparshkhurana5390 2 роки тому +1

    This is actually perfect, I had a good understanding of this but this has cleared all my doubts and solidified my knowledge.

  • @luckytechpro9537
    @luckytechpro9537 4 роки тому

    U reminded me one of my funny Lecturer whos teaching me this Unit{Database}...Above all, u r amazing. Thanks, Sir.Greetings from Kenya

  • @Jacob-ix4gp
    @Jacob-ix4gp 3 місяці тому

    I am in one of his classes now. Great professor.

  • @GenjaOrigins
    @GenjaOrigins 6 місяців тому

    10:50 And if 1-N or N-1 and not N-N what you? not bridge table?

  • @thearchibaldtuttle
    @thearchibaldtuttle 6 років тому +1

    Important to know: Reverse engineering for databases exists only up too Visio 2010. That was an extremely good feature to create nice looking ERD for documentation purposes.

  • @akarshjaiswal1622
    @akarshjaiswal1622 4 роки тому +4

    Amazing...
    Love from India !

  • @johnkelly8919
    @johnkelly8919 10 місяців тому

    No Address2 for unit, apartment or suite information or an address behind a street front building?

  • @GoodDeedsLeadTo
    @GoodDeedsLeadTo 2 роки тому

    Oracle Database is a set of tables with foreign key primary key relationship and at the O/S level it is a file with dbf extension?
    &
    database server at the O/S level is a set of dbf and files with few other extensions?

  • @PapaMufti
    @PapaMufti 3 роки тому

    I have hard time understanding the second version of the model. How is the address tied to customer ID? What specifically is the address attribute for? Street name, house number, flat number? How is this situation different from having customer name in one field as it is in the first model?

  • @gloryjeansqueaky
    @gloryjeansqueaky 8 років тому +17

    This was incredibly helpful! I am designing my first database for my mother's daycare business. I liked the idea of separating the Orders from the Products, but I don't know if I that's how I should create the classes in my database. I have a table with classID and location and capacity. Then I have a Student Table and a Teacher Table. Right now I have tacked on to each the Student and Teacher tables a field called ClassID where you can plug in the ClassID to which they belong at the moment. But then I was thinking after watching this video, if it's better design to have a separate table which has ClassID and PersonID only. That personID can either be a TeacherID or a StudentID so that the table would reflect every person tied to that particular class. I'm double thinking my design.

    • @GoSparker
      @GoSparker  8 років тому +14

      MGELC - one of the most common responses to database design questions is "it depends." In your case if you have the ClassID in the Student and Teacher tables then you can only have one instance of a class for each student and teacher. In other words, you won't see history. For example, I can put the ClassID into the Student table and see what class the student is currently in, but if you move the student to another class then the ClassID changes in the Student table and you don't have a record of the previous student class relationship. If you have a bridge table between Student and Class (let's call it StudentClass) and have the StudentID and ClassID in the StudentClass table as well as a start and end date you will be able to keep track of all of the classes a student has been in. Similar with the Teacher and Class relationship. The other thing that the StudentClass table can do for you is allow a student to be in more than one class at a time.Another part of the "it depends" question is how important is it to know if a student was in another class? If you don't care then you may not have a use for the StudentClass table and perhaps could just use a comment field in one or more of the tables to handle some details. The problem with comment fields is that they can be hard to use in queries - particularly in joins. Hope this helps.

    • @kbdavis0007
      @kbdavis0007 8 років тому +3

      MGELC a link table is what you are looking for 😀 a table that just provide links to related data

    • @lasujacob
      @lasujacob 7 років тому

      you can connect with me on whatsapp +256787867812

    • @ambars5747
      @ambars5747 4 роки тому

      @@GoSparker That's a great answer and very helpful. Thanks!

  • @flexi3303
    @flexi3303 3 роки тому

    First letter of each word is capital in ZipCode is Pascal Naming convention,
    Camel case naming convention is zipCode first letter will be small of the word, after that first letter will be capital

  • @RockstarGunner09
    @RockstarGunner09 6 років тому +2

    Shouldn't the CustomerID in CustomerPhone be a FK not a PK? Same as the OrderID and ProductID in the OrderProducts table?

    • @littleleaves9886
      @littleleaves9886 2 роки тому

      if we make them FK then we have to introduce their own ID like customerPhoneID, orderProductsID

  • @amitjoshi5798
    @amitjoshi5798 6 років тому +2

    Best tutorials one can have. Thanks a ton!

  • @AbdurRahim-ot5gp
    @AbdurRahim-ot5gp 4 роки тому

    A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control how to fix

  • @Solrac77777
    @Solrac77777 4 роки тому +4

    I appreciate the great explanation, thank you! Saved me allot of time :)

  • @nathanunderbsd5972
    @nathanunderbsd5972 5 років тому +1

    why all PK there is no FK?

  • @nickfleming3719
    @nickfleming3719 5 років тому

    So, lets say we have a local business where the # of zip codes is limited but you can have thousands of customers. Would it save memory and increase search speed to store the list of zip codes in a separate table and just have a zip_id field for each customer point to a row in the zip code table, instead of storing the whole zip code for each customer?

    • @GoSparker
      @GoSparker  5 років тому

      Preface this response with 'it depends', but in general you would be adding complexity. Consider looking at this another way - don't store city and use zip code to look up city. For a local business the volume is likely such that the space savings for having a zip lookup would be inconsequential. With regard to speed - adding joins can actually slow down queries so often the design question is balancing space and speed. In other words, saving space in a way that increases joins may slow down response time. So you often find that a gain in one (space) area is a loss in another (speed). Usually it is best to keep your design as simple and flexible as possible so that as it goes through its life cycle you are in a position to be able to adapt/modify the design for new requirements that emerge.
      A zip_id would likely be some type of integer field. The zip code may be an integer or character (international zip codes). So you would be adding an additional field with zip_id that probably consumes approximately the same space as zip code. You would also need to maintain a zip code table that may end up being all zip codes. So bottom line is unless there are unusual requirements I wouldn't create a zip_id field that points to zip code.

  • @terrysennette5938
    @terrysennette5938 5 років тому +1

    Excellent work! Thank you so much for this training!!!

  • @runthomas
    @runthomas 6 років тому

    thank heavens i came across you......i am designing a datbase for django ...and i was completely wrong in my approach and duplicating and throwing htings around all over the place...now im gonna start again and try and get a better design.

  • @shwetha2289
    @shwetha2289 6 років тому

    Why we can't we use both orderID and product ID as composite key in this?why we use line No and orderID as composite key?

  • @Brad10
    @Brad10 7 років тому +11

    Thank you for the upload! Do you know if Dr. Gogolin has a series of videos or a channel anywhere? I was able to grasp the concepts he was talking about a lot better than most instructors.

  • @shashankkr1008
    @shashankkr1008 Рік тому

    Very good explanation, thank you!

  • @lukealadeen7836
    @lukealadeen7836 2 роки тому

    What about data lakes?

  • @cgme7076
    @cgme7076 4 роки тому +3

    Extremely concise. Thank you!

  • @Mark4Jesus
    @Mark4Jesus 3 роки тому

    Thanks for this lesson. I wasn't clear on why 2 phone numbers is bad?

  • @slablife
    @slablife 5 років тому

    Great video! I would like to correct you on one thing @ 15:50... you are using PascalCase and not camelCase... notice the difference?

    • @GoSparker
      @GoSparker  5 років тому +1

      Pascal Case is a subset of Camel Case, which is the more widely known term (although Pascal Case could very well be more utilized in database naming). To be more complete with the various ways fields are often named: camelCase, PascalCase, snake_case, kebab-case ... but even within these there are variations and different types of requirements depending on many things including programming language and configuration of installation. One other point - many DBMS installs are not case sensitive by default. Could almost make a video just on naming conventions... thanks for bringing this up Mike.

    • @shreyasjejurkar1233
      @shreyasjejurkar1233 5 років тому

      Yes it's PascalCase.

  • @watummoses6911
    @watummoses6911 4 роки тому +1

    thank for this wonderful tutorial

  • @truckstomotorcycleswithtom3197
    @truckstomotorcycleswithtom3197 3 роки тому

    Just learning SQL and this helps on the many-many relationship

  • @suriyav9157
    @suriyav9157 3 роки тому

    Thank you for explaining based on practical corporate experience

  • @Fractal80Y
    @Fractal80Y 7 років тому

    Is it correct that OrderProducts and Products should NOT be plural?

  • @ciaranmckenna5034
    @ciaranmckenna5034 8 років тому

    Great work, if you have anymore examples please do share them. In your 3NF what exactly happened to the multiple values for phone numbers, if a customer had a work and home number what happened there?

    • @GoSparker
      @GoSparker  7 років тому +1

      Go to about 8:10 and the CustomerPhone table as a design option. There is a Type column that you can use to distinguish between work, home, cell, etc. The design could be expanded to have a Type table with TypeID and Type and then you would put TypeID in the CustomerPhone table rather than Type. This expanded design would help control and standardize the Type(s) that are used in the applications.

    • @HaploBartow
      @HaploBartow 7 років тому

      Thanks for this comment; I feel like your description in the video did not explain the improvement very well, but this comment helps cover that gap.

  • @purpleplasmaindustries4460
    @purpleplasmaindustries4460 4 роки тому

    Fantastic video. Thank you for creating this!!

  • @DataRevolution10
    @DataRevolution10 3 роки тому

    Awesome way of explaining...Thanks!

  • @idrissnyanja5369
    @idrissnyanja5369 6 років тому

    Sorry sir .once you are given to design an ERD of an organization or companies .and you want all departments to be represented by it table how do we count relation between the attribute (many to many) or (one to one) or (one to many)? Please sir help me

  • @kulkarnik1
    @kulkarnik1 6 років тому

    Thanks for video. But I have a question. How can you say that customer table violates 2nf? I don't think it does because employer name is totally dependent on customer key and not the subset of the candidate key. Please let me know. Thanks!

    • @littleleaves9886
      @littleleaves9886 2 роки тому

      I agree with you. But if you need to keep full details of Employer, better move that to separate table.

  • @davidyergensen4509
    @davidyergensen4509 6 років тому +2

    This was a very helpful and informative video. Thank you. I'm looking forward learning more.

  • @ramseybeing
    @ramseybeing 5 років тому +3

    Thanks this gave me some clarity!

  • @cphoenix0
    @cphoenix0 8 років тому +2

    excellent, thank you for making this video, very helpful!

  • @kennethmcquade4341
    @kennethmcquade4341 6 років тому +4

    this is brilliant, thank you

  • @himanshumewari7425
    @himanshumewari7425 8 років тому

    can we use customer ID , Product Id in a single table?? of order ID

  • @mudaprince3067
    @mudaprince3067 2 роки тому

    i want to prepare supply chain project can you help me please

  • @peterlyon185
    @peterlyon185 2 роки тому

    Thank you so much for this video 🙏🏼

  • @chrisscottdoes
    @chrisscottdoes 3 роки тому

    Excellent video! Thank you

  • @Paretozen
    @Paretozen 6 років тому

    Why is Excel not a database? Even a piece of paper can be a database right?

    • @krissicura842
      @krissicura842 6 років тому

      No, a piece of paper is not a database. The easiest way for me to explain is to say that the data in an excel spreadsheet can be used BY a database to manipulate or manage that data, but it is not in and of itself a database.
      Think of a spreadsheet as being flat, again, like a sheet of paper but a database as being multi-dimensional.
      When you want a simple "list" a spreadsheet will do just fine, but as your needs grow and the data points begin to relate in different ways, you may need a database to organize and report on that data.

  • @jonghunpark1403
    @jonghunpark1403 5 років тому +4

    This is just awsome! Thanks so much.

  • @granand
    @granand 4 роки тому

    Thank you .. Sir. Just on lighter note: :-) Sai Reddy .. 8342 Cricket as address :-) :- ) I come from same city Hyderabad and I confirm this is example not real data .. zip code there 6 digits?

  • @zabihullahrezaei361
    @zabihullahrezaei361 6 років тому

    may did you make a vedio for a ER diagram of computer Store.

  • @njoroge2061
    @njoroge2061 5 років тому

    Am a newbie in Ms access i think the data design is quite helpful

  • @ashwinimudaliar9801
    @ashwinimudaliar9801 2 роки тому

    so nicely explained..thanks!

  • @prasathj7436
    @prasathj7436 4 роки тому

    Fantastic tutorial Sir. Thanks a lot.

  • @luisegonzalez3805
    @luisegonzalez3805 6 років тому +2

    A very nice explanation, thank you for the tutorial.

  • @santoshkumarkar39
    @santoshkumarkar39 4 роки тому

    What is the software name...

    • @ChristopherJohnsonIsAwesome
      @ChristopherJohnsonIsAwesome 4 роки тому

      Microsoft Visio was the diagram tool and SQL Server Management Studio was the database tooling

    • @GoSparker
      @GoSparker  4 роки тому

      @@ChristopherJohnsonIsAwesome Yes Santosh - Christopher is correct.

  • @kazititu
    @kazititu 6 років тому +2

    Sir , Thank you for the lecture, it cleared up lot of things that i didnt understand before? do you have any other videoes on relational databse design and implementation?

  • @BackToBackSWE
    @BackToBackSWE 5 років тому +2

    thanks for this!

  • @hajimunirsediqi1492
    @hajimunirsediqi1492 6 років тому +2

    Hallo, i watched ur vedio , it is great thank u from ur teachng, want a database which is already created in postgresql , then i can fallow step by step.
    thank u

  • @chandra3370
    @chandra3370 4 роки тому

    Thanks for this awesome lecture

  • @lampshade5437
    @lampshade5437 6 років тому +1

    Super helpful, thanks!

  • @masulethomas4507
    @masulethomas4507 3 роки тому

    best example for bigginers

  • @BRP-Moto-Tips
    @BRP-Moto-Tips Рік тому

    Thanks mate 👑

  • @victoriagabaldon8469
    @victoriagabaldon8469 2 роки тому

    Thank you Super Helpful!

  • @JamesAutoDude
    @JamesAutoDude 3 роки тому

    I learned this too late after my database grew too much 😭 the struggle is real

  • @joshuafancher3111
    @joshuafancher3111 6 років тому +4

    Thanks for the tutorial!

  • @ORACLEAPEX410
    @ORACLEAPEX410 4 роки тому

    nice video
    keep it up

  • @md.imanali9998
    @md.imanali9998 5 років тому

    Very helpful. thank you for making this video.

  • @biruksisay3717
    @biruksisay3717 8 років тому +4

    Thank you !

  • @aakashkumar7052
    @aakashkumar7052 7 років тому +1

    please any body give me a learning managment system ERD in database

  • @mochasteak
    @mochasteak 4 роки тому

    Great explainer, thanks.

  • @karolidomisiani282
    @karolidomisiani282 7 років тому

    Thanks for your good work

  • @MrKatdar
    @MrKatdar 5 років тому

    great explanation, thanks

    • @brandonmohammed2234
      @brandonmohammed2234 3 роки тому

      HELL IS REAL
      HEAVEN IS REAL
      GOD IS REAL
      JESUS IS THE SON OF GOD
      ALL THOSE WHO ARE NOT SAVED WILL GO TO HELL AND HELL IS TORTURE FOR ETERNITY BURNING IN NEVER ENDING FIRE, ONCE YOU ARE IN HELL THERE IS NO WAY OUT, HOWEVER RIGHT NOW IT IS POSSIBLE TO PREVENT YOURSELF FROM GOING TO HELL AND BECOMING SAVED AND THERE IS ONLY ONE WAY OUT AND THIS WAY IS JESUS, JESUS DIED AND ROSE AGAIN AS A SACRIFICE FOR OUR SINS SO THAT WE DON'T HAVE TO GO TO HELL AND WE CAN HAVE EVERLASTING LIFE SO WE CAN SPEND ETERNITY WITH HIM IN HEAVEN WHICH IS PURE PARADISE. ALL YOU HAVE TO DO IS TO PUT YOUR FAITH IN JESUS AND TRUST THAT HE WILL SAVE YOU AND THE HOLY SPIRIT WILL MAKE HIS HOME IN YOUR HEART AND LEAD YOU ON THE RIGHT PATH. DON'T DELAY IN TRUSTING JESUS BECAUSE WE CANNOT TELL WHEN IT IS TOO LATE. JESUS IS COMING SOON PLEASE BE READY AND MAKE THE RIGHT CHOICE

  • @true_human_007
    @true_human_007 6 років тому

    thanking you
    Very helpful video

  • @dadasajad8741
    @dadasajad8741 5 років тому +1

    thank you sir.

  • @arbabqaisar654
    @arbabqaisar654 Рік тому

    Not all heroes wear capes

  • @Bursadesain
    @Bursadesain 7 років тому

    good video, thanks

  • @ArthurFreitag
    @ArthurFreitag 6 років тому

    really well explained

  • @souryamajumdar3225
    @souryamajumdar3225 7 років тому

    Very good tutorial

  • @NagimuKafeero
    @NagimuKafeero Рік тому

    Wow thanks

  • @santoshkumarkar39
    @santoshkumarkar39 4 роки тому

    Excellent... E.. X... C.. ellent..

  • @malchicken
    @malchicken 5 років тому

    Very clear. Thank you.

  • @moz8405
    @moz8405 8 років тому +2

    Lovely. :)

  • @umakiruba6653
    @umakiruba6653 7 років тому

    Great example...

  • @ydherdn
    @ydherdn 4 роки тому

    Thank You

  • @y4cministriesinternational965
    @y4cministriesinternational965 8 років тому

    i like this thanks allot

  • @Bursadesain
    @Bursadesain 7 років тому +1

    good content, thank you

  • @harshilparmar9076
    @harshilparmar9076 5 років тому

    thanks brother

  • @atultyagi4944
    @atultyagi4944 11 місяців тому

    perfect

  • @DrDreadz
    @DrDreadz 7 років тому

    really cool

  • @zabihullahrezaei361
    @zabihullahrezaei361 6 років тому

    very good

  • @selimreza7163
    @selimreza7163 8 років тому

    next part plz

  • @r2dclub69
    @r2dclub69 7 років тому

    nice video

    • @brandonmohammed2234
      @brandonmohammed2234 3 роки тому +1

      HELL IS REAL
      HEAVEN IS REAL
      GOD IS REAL
      JESUS IS THE SON OF GOD
      ALL THOSE WHO ARE NOT SAVED WILL GO TO HELL AND HELL IS TORTURE FOR ETERNITY BURNING IN NEVER ENDING FIRE, ONCE YOU ARE IN HELL THERE IS NO WAY OUT, HOWEVER RIGHT NOW IT IS POSSIBLE TO PREVENT YOURSELF FROM GOING TO HELL AND BECOMING SAVED AND THERE IS ONLY ONE WAY OUT AND THIS WAY IS JESUS, JESUS DIED AND ROSE AGAIN AS A SACRIFICE FOR OUR SINS SO THAT WE DON'T HAVE TO GO TO HELL AND WE CAN HAVE EVERLASTING LIFE SO WE CAN SPEND ETERNITY WITH HIM IN HEAVEN WHICH IS PURE PARADISE. ALL YOU HAVE TO DO IS TO PUT YOUR FAITH IN JESUS AND TRUST THAT HE WILL SAVE YOU AND THE HOLY SPIRIT WILL MAKE HIS HOME IN YOUR HEART AND LEAD YOU ON THE RIGHT PATH. DON'T DELAY IN TRUSTING JESUS BECAUSE WE CANNOT TELL WHEN IT IS TOO LATE. JESUS IS COMING SOON PLEASE BE READY AND MAKE THE RIGHT CHOICE

  • @Endoe.McKronic
    @Endoe.McKronic 5 років тому

    I fell asleep like 4 times.... Sheeeesh

  • @cubiCampla
    @cubiCampla 5 років тому

    epic

  • @nothanks8223
    @nothanks8223 Рік тому

    👀

  • @roselpadilla
    @roselpadilla 3 роки тому

    Ferris State > hArVard

  • @valthalin7613
    @valthalin7613 3 роки тому

    Data viz, because I'm dumb.
    customer:
    id=1(pk); fname=paul
    id=2(pk); fname=matt
    Products:
    id=16,(pk) name=sunglasses
    id=17(pk), name=Nintendo switch
    order:
    id=1(pk); customer_id=1, date=2021
    id=2(pk); customer_id=2, date=2020
    order_products:
    [order_id=1, product_id=16](pk); qty=1 "paul order 1 sunglasses"
    [order_id=1, product_id=, 17](pk); qty=1 - ON Conflict (set qty += 1) -- update qty for duplicate
    [order_id=1, product_id=, 17](pk); qty=1 + 1 "paul ordered 2 Nintendo switch"
    [order_id=2, product_id=, 17](pk); qty=1 "matt ordered 1 Nintendo switch"