Well, we already have a users table, and we can think of a user as a specific entity within our app a 'user' is someone who uses our app.What entities might we define for our SQL Book application? For the purposes of this book we're going to try and keep things simple and draw a direct correlation between an entity and a single table of data in a real database however, the data for a single entity might be stored in more than one table. EntitiesĪn entity represents a real world object, or a set of data that we want to model within our database we can often identify these as the major nouns of the system we're modeling. But what do we mean by entities, and how do different entities relate to each other? Let's find out. Database DesignĪt a high level, the process of database design involves defining entities to represent different sorts of data and designing relationships between those entities. We know that we want to split the data for our application across multiple tables, but how do we decide what those tables should be and what relationships should exist between them? When answering questions such as these it is often useful to zoom out and think at a higher level of abstraction, and this is where the process of database design comes in. The mechanism for carrying out normalization is arranging data in multiple tables and defining relationships between them.The reason for normalization is to reduce data redundancy and improve data integrity.These rule-sets, known as 'normal forms', are beyond the scope of this book for now there are two important things to remember: Normalization is a deep topic, and there are complex sets of rules which dictate the extent to which a database is judged to be normalized. The process of splitting up data in this way to remove duplication and improve data integrity is known as normalization. ![]() How do we deal with this situation? The answer is to split our data up across multiple different tables, and create relationships between them. For example what if for one of the 'My Second SQL Book' checkouts the title is entered as 'My 2nd SQL Book' instead, or a typo had been made with the isbn on one of the rows? From looking at the data in the table, how would we know which piece of data is correct? It's a similar story with the book data, if more than one person checks out a book, such as with 'My Second SQL Book', we have to repeat the book title, author, isbn, and published date.ĭuplicating data in this way can lead to issues with data integrity. For each book that a user checks out, we have to repeat all of the user data in our table. Wow, that's a lot of information all in one table! There are other issues here as well, such as duplication of data (often referred to as 'redundancy'). To implement some of these requirements we could simply try adding more columns to our users table the resulting table might look a little like this: Our app will be used to manage a library of SQL books and allow users to check out the books and also review them. In reality, the requirements of our application will mean that we need to store a lot more data than that. It stores a name for the user, whether their account is enabled or not, when they last logged in, and an id to identify each user record. NormalizationĪt this point, our users table doesn't need to hold that much data for each user in our system. In this chapter we'll explore the reasons for having multiple tables in a database, look at how to define relationships between different tables, and outline the different types of table relationships that can exist. The majority of databases you'll work with as a developer will have more than one table, and those tables will be connected together in various ways to form table relationships. ![]() This means that if the constraint is deferred there is a big difference between RESTRICT and NO ACTION.įor immediate constraints there is probably no difference unless one could perhaps write a trigger program of some sort that executes within the context of the statement that resolves the integrity violation before the end of the statement.Thus far in this book, all the work we've done has been with a single database table. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. It might be advised to read the entire page. ![]() That depends on the circumstance see here for the difference.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |