If the tables are innodb you can create it like this: CREATE TABLE accounts( account_id INT NOT NULL AUTO_INCREMENT, customer_id INT( 4 ) NOT NULL , account_type ENUM( ‘savings’, ‘credit’ ) NOT NULL, balance FLOAT( 9 ) NOT NULL, PRIMARY KEY ( account_id ), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ) ENGINE=INNODB; You have to specify … Read more
A theta join allows for arbitrary comparison relationships (such as ≥). An equijoin is a theta join using the equality operator. A natural join is an equijoin on attributes that have the same name in each relationship. Additionally, a natural join removes the duplicate columns involved in the equality comparison so only 1 of each … Read more
If you have a “grid” that you want to populate completely, like size and color information for a particular article of clothing: select size, color from sizes CROSS JOIN colors Maybe you want a table that contains a row for every minute in the day, and you want to use it to verify that a … Read more
There actually is conceptual reasoning behind both styles. Wikipedia on the relational model and graph databases gives good overviews of this. The primary difference is that in a graph database, the relationships are stored at the individual record level, while in a relational database, the structure is defined at a higher level (the table definitions). … Read more
1 to 0..1 The “1 to 0..1” between super and sub-classes is used as a part of “all classes in separate tables” strategy for implementing inheritance. A “1 to 0..1” can be represented in a single table with “0..1” portion covered by NULL-able fields. However, if the relationship is mostly “1 to 0” with only … Read more
Your understanding is correct. You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two … Read more
Just promise yourself that you will never try to map a relational data model to a NoSQL database like MongoDB or CouchDB… This is the most common mistake developers make when evaluating emerging tech. That approach is analogous to taking a car and trying to use it to pull your cart down the road like … Read more
Yes, it a vice versa. It depends on which side of the relationship the entity is present on. For example, if one department can employ for several employees then, department to employee is a one to many relationship (1 department employs many employees), while employee to department relationship is many to one (many employees work … Read more
I have done this two ways in the past – a single row table and a key/value pair table – and there are positives and negatives to each approach. Single Row positive: the values are stored in the correct type positive: it is easier to deal with in code (due to the above) positive: default … Read more
Not all data is relational. For those situations, NoSQL can be helpful. With that said, NoSQL stands for “Not Only SQL”. It’s not intended to knock SQL or supplant it. SQL has several very big advantages: Strong mathematical basis. Declarative syntax. A well-known language in Structured Query Language (SQL). Those haven’t gone away. It’s a … Read more