You have at least these five options for modeling the type hierarchy you describe: Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row. Class Table Inheritance: one table for Products, … 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
Assuming this table: CREATE TABLE students ( student_id SERIAL PRIMARY KEY, player_name TEXT ); There are four different ways to define a foreign key (when dealing with a single column PK) and they all lead to the same foreign key constraint: Inline without mentioning the target column: CREATE TABLE tests ( subject_id SERIAL, subject_name text, … Read more
Table • Name recently learned singular is correct Yes. 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 they work together they … Read more
You have a few options, all varying in “correctness” and ease of use. As always, the right design depends on your needs. You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. You could create M:M reference tables enabling both ticket:user and ticket:group relationships. Perhaps in … Read more
Definitely Relational. Unlimited flexibility and expansion. Two corrections, both in concept and application, followed by an elevation. Correction It is not “filtering out the un-needed data”; it is selecting only the needed data. Yes, of course, if you have an Index to support the columns identified in the WHERE clause, it is very fast, and … Read more
For INNER joins, no, the order doesn’t matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.*, b.*, c.*. For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters – and (updated) things are much more complicated. First, outer joins are not commutative, so … Read more
With with() is for eager loading. That basically means, along the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of … 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
One-to-one: Use a foreign key to the referenced table: student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a # “link back” if you need You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating … Read more