Let's take a look at the design of these four tables together, we will then explain how each of these relationships is modeled individually. A Book is/has been checked out by MANY users.One User has MANY books that he/she may have checked out or returned.Now that we know which tables we need, we also need to map the relationships between these tables, sometimes known as entities.
We've mentioned relational databases a lot, but haven't talked a lot about what that relational part means. If we break out the data into separate tables, we need tables for Users, Addresses, Books and Reviews. It makes sense to store them in separate tables and provide a link between the tables so we can figure out which review or book belongs to which user. Furthermore, we still need to add more information, such as author and checkout_date of the book. For instance, Jane Smith has to be inserted for every book and review for that user. With just 3 records, the data is cluttered with duplicate information. +-+-+-+-+-+-+-Ĥ | John Smith | f | 19:01:52.341599 | Chaos | What is the butterfly effect? | 5 | 2 Market Streetĥ | Jane Smith | t | 19:05:55.439876 | Simple Taoism | About Living in balance | 5 | 3 Market StreetĦ | Jane Smith | t | 19:08:06.039351 | The Storyteller | Memories of WWII | 4 | 3 Market Street Id | username | enabled | last_login | book_title | review_content | rating | address Let's see what the data looks like library=# SELECT * FROM users VALUES('Jane Smith', true, 'The Storyteller', 'Memories of WWII', 4, '3 Market Street') INSERT into users(username, enabled,book_title, review_content,rating,address) VALUES('Jane Smith', true, 'Simple Taoism', 'About Living in balance', 5, '3 Market Street') INSERT into users(username, enabled, book_title, review_content, rating, address) VALUES('John Smith', false, 'Chaos', 'What is the butterfly effect?', 5, '2 Market Street') Next, we need to insert some data into the table INSERT into users(username, enabled, book_title, review_content, rating, address) Last_login | timestamp without time zone | not null default now() Username | character varying(25) | not null Id | integer | not null default nextval('users_id_seq'::regclass)
First we need to add these columns using the ALTER TABLE command. We could start adding these pieces to the users table, resulting in a table with many columns. In this chapter we want to add several more pieces of data to our database. We'll still be working with the familiar library database and users table from previous chapters, but with a blank users table to start with. Follow the directions above in the info box to retrieve starter data for this chapter. With that in mind, we'll start fresh in this chapter. In the chapter on altering a table, we ended up changing our users database in various ways. If the table doesn't exist, PostgreSQL will ignore the command and move on to executing the rest of the backup file. These are included in case that table already exists. There are some commands in place at the start of the backup file for altering and removing the users table. When running the above command, you may see the following message: ERROR: relation "ers" does not exist.
#POSTGRESQL CREATE TABLE FROM QUERY DOWNLOAD#
First download the backup file for this chapter from here. The library database can be restored from a backup file. The library database is needed to work through this chapter's content. If you don't already have a database named "library", then make sure to run createdb library from the terminal before starting this chapter.
0 kommentar(er)
