How to learn database design?

Most current SQL database courses focus on syntax, functions, and query methods. My current project needs to "design a database", so I need to learn the knowledge of database design. Does anyone have any learning experience in database design?

Comments

  • Designing a database schema (I assume this is what you're after) depends on what your dataset is. This can vary things quite drastically, for example in some cases, a relational database like MySQL works well, in other cases, you might need to consider other database types, like graph databases.

    There are some good rules of thumb to start off with:

    • One field, one item of data. Minimise storing concatenated or serialised data within a database field. This allows you to optimise queries and not have to rely on post-query parsing.
    • Encourage strict data types. Don't store different data types in a single field. This helps database optimisation.
    • 1:1 relationships should be kept in the same table. This prevents unnecessary JOINs.
    • Non-1:1 relationships are split into different tables. This prevents data duplication and helps improve data integrity.

    Other more specific advice, perhaps related to setting foreign key relationships, primary keys, views, indexes, and so on, require more specific questions :) All the best on your course!

  • Not sure how this relates to open source architecture, but I will respond anyway. I have 20 years of experience doing this.

    Normally the key thing you want to do is figure out what your queries are going to be - how often what kind of data will need to be retrieved or calculated, and based upon which input. Another key thing to determine is how frequently new data will be added or data will change.

    Generally, you can pretty much guarantee you won't get more than 10 queries per second of a finite number of types. People almost always over-design databases. Forget that. If you can get away with it, just store data in files. The operating system, web server, proxy servers and the client will cache these perfectly, and you will be able to serve many clients very well.

    If you can't get away with that, use SQLite, one of the best written pieces of software in the universe. A cheap computer like a phone has enough computing power to serve huge numbers of clients simultaneously, and all phones run SQLite. There is no performance reason and few other reasons to choose anything more than SQLite unless you really need to add huge amounts of data and make diverse queries quite regularly.

    In that (rare) case, use a regular SQL RDBMS like Postgres or MariaDB. Nobody ever gets fired for going straight to an SQL RDBMS, but they should be.

    Moultshanmamavpajic
  • @globalcitizen said:
    Not sure how this relates to open source architecture, but I will respond anyway. I have 20 years of experience doing this.

    Normally the key thing you want to do is figure out what your queries are going to be - how often what kind of data will need to be retrieved or calculated, and based upon which input. Another key thing to determine is how frequently new data will be added or data will change.

    Generally, you can pretty much guarantee you won't get more than 10 queries per second of a finite number of types. People almost always over-design databases. Forget that. If you can get away with it, just store data in files. The operating system, web server, proxy servers and the client will cache these perfectly, and you will be able to serve many clients very well.

    If you can't get away with that, use SQLite, one of the best written pieces of software in the universe. A cheap computer like a phone has enough computing power to serve huge numbers of clients simultaneously, and all phones run SQLite. There is no performance reason and few other reasons to choose anything more than SQLite unless you really need to add huge amounts of data and make diverse queries quite regularly.

    In that (rare) case, use a regular SQL RDBMS like Postgres or MariaDB. Nobody ever gets fired for going straight to an SQL RDBMS, but they should be.

    So cool!Maybe I will develop an optimized version of ifc in the future (let's think IFC is also a kind of database)

  • @Moult said:
    Designing a database schema (I assume this is what you're after) depends on what your dataset is. This can vary things quite drastically, for example in some cases, a relational database like MySQL works well, in other cases, you might need to consider other database types, like graph databases.

    There are some good rules of thumb to start off with:

    • One field, one item of data. Minimise storing concatenated or serialised data within a database field. This allows you to optimise queries and not have to rely on post-query parsing.
    • Encourage strict data types. Don't store different data types in a single field. This helps database optimisation.
    • 1:1 relationships should be kept in the same table. This prevents unnecessary JOINs.
    • Non-1:1 relationships are split into different tables. This prevents data duplication and helps improve data integrity.

    Other more specific advice, perhaps related to setting foreign key relationships, primary keys, views, indexes, and so on, require more specific questions :) All the best on your course!

    Your suggestion is very good and clear, but I am puzzled how to learn this kind of thinking. Should I study the relevant courses seriously?

  • @shanmama there is an IFCSQLite schema. It is not yet really practical, but it is being worked on nonetheless. IFC is absolutely just a database :)

    The best advice I can offer is to learn by doing :) Enjoy your course, create databases, write queries, and try again when things don't work.

  • @Moult said:
    @shanmama there is an IFCSQLite schema. It is not yet really practical, but it is being worked on nonetheless. IFC is absolutely just a database :)

    The best advice I can offer is to learn by doing :) Enjoy your course, create databases, write queries, and try again when things don't work.

    OK fine!Learn by doing

  • edited June 2020

    I found a book written by Thomas Connolly. Some Chinese mainland friends said it is a classic book to introduce database design.

    Database Systems: A Practical Approach To Design, Implementation And Management,

    Now I downloaded this book (pdf file) from researchgate. If someone wants to study together, I will send it to you by e-mail

    Moult
  • All the best @shanmama - if you do anything cool with your databases in AEC, be sure to post it here!

  • I have a degree in CS. I thought this book was the silliest thing I've ever seen on my library's shelf. After skimming through it, this is now the book I recommend to anyone asking about database design:
    The Manga Guide to Databases

    CyrilMoultReD_CoDEtlang
  • @PaulTOB said:
    I have a degree in CS. I thought this book was the silliest thing I've ever seen on my library's shelf. After skimming through it, this is now the book I recommend to anyone asking about database design:
    The Manga Guide to Databases

    It is so cute!
    Now I found it .pdf version. If someone wants to study together, I will send it to you by e-mail

  • Just reading through this thread. I made a Django python hour registration application two years ago which required data modelling/architecture.
    https://www.bol.com/nl/nl/p/pragmatisch-modelleren-met-uml/9200000059268458/
    This book was really helpful, very conceptual. Can't find any English language versions however.
    However this skill seems somewhat useless in the world of IFC

Sign In or Register to comment.