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
Your pizza can have exactly three topping types: one type of cheese one type of meat one type of vegetable So we order two pizzas and choose the following toppings: Pizza Topping Topping Type ——– ———- ————- 1 mozzarella cheese 1 pepperoni meat 1 olives vegetable 2 mozzarella meat 2 sausage cheese 2 peppers vegetable … Read more
Table • Name recently learned singular is correct Yes. Beware of the heathens. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. Some of the wonderful things about Standards are: they are all integrated with each other … 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
1 Concept You have misunderstood some basic concepts, and the difficulties result from that. We have to address the concepts first, not the problem as you perceive it, and consequently, your problem will disappear. auto incremented IDs, which are of course primary keys. No, they are not. That is a common misconception. And problems are … Read more
You should always enforce referential integrity by using “normal” FOREIGN KEYs. In a nutshell, FOREIGN KEYs have the following advantages: They are already implemented within the DBMS. They are declarative, self-documenting and “obvious”. They cannot be bypassed (unless explicitly disabled or dropped). They are correct. They are fast. They support cascading referential actions (such as … Read more
This is called the Observation Pattern. Three objects, for the example Book Title=”Gone with the Wind” Author=”Margaret Mitchell” ISBN = ‘978-1416548898’ Cat Name=”Phoebe” Color=”Gray” TailLength = 9 ‘inch’ Beer Bottle Volume = 500 ‘ml’ Color=”Green” This is how tables may look like: Entity EntityID Name Description 1 ‘Book’ ‘To read’ 2 ‘Cat’ ‘Fury cat’ 3 … Read more
If you’re going to be doing any syncing between databases with occasionally connected apps, then you should be using GUIDs for your primary keys. It is kind of a pain for debugging, so apart from that case I tend to stick to ints that autoincrement. Autoincrement ints should be your default, and not using them … Read more
Denormalising to improve performance? It sounds convincing, but it doesn’t hold water. Chris Date, who in company with Dr Ted Codd was the original proponent of the relational data model, ran out of patience with misinformed arguments against normalisation and systematically demolished them using scientific method: he got large databases and tested these assertions. I … Read more
Generally, there are two strategies to handle a situation like this: Use Exclusive FKs Essentially, each of the possible parent tables will have its own, separate foreign key in the child table, and there is a CHECK enforcing exactly one of them is non-NULL. Since FKs are only enforced on non-NULL fields (meaning, when a … Read more