Apologies if my wording is very awkward/non-fitting for a programmer – I’m just a SQL noob who’s taking a beginners’ class. I’m using PHPMyAdmin to design an e-commerce database in which the minimum age of a customer is 18 yo. Because age is a calculated column, I want to record the DOB and enforce a CHECK constraint. I tried this
Tag: database-design
Query updated records in Database
Currently all our tables have a created_at and updated_at timestamp, e.g. The updated_at field is modified with every INSERT. This allows us to query all Cart’s which have been updated since a particular point in time with something like this: However this would not capture updates to FK relationships such as CartItemss and Users. Is there a nice way to
SQL Database Design Union Data Type? [closed]
This may be a basic question, but I’m curious about what the most reasonable “preferred” way to design a database with a union type is. What I mean is consider a table Post which has only 1 piece …
Database table design for ‘availability’ within time ranges per day
I am working on a meeting scheduling web-app, where a person can share their availability for specific time ranges within the day. E.g. A user is available 3 time-ranges within the day, from 09:10 to 10:00, 13:00 to 14:00 and 16:30 to 17:15. This can go on for 6 days per week, from Monday to Saturday. What I came up
How to declare the elements of an array non-nullable?
In the following simple table It’s possible to insert null as an element of things: But I’d like to not allow this. Changing the definition to the following, however, Only prevents this which is not what I want. (I still want to allow that.) So how can I declare not the column, but the elements of the array column to
Making sense of database table references with foreign and primary keys
I am relatively new to database logic, and am trying to figure out how multiple tables should/shouldn’t reference each other. I have a table, ‘Book’, that should have columns: I want each book to have the possibility to have 1 or more genres. I want each book to have the possibility to have 1 or more authors. I want each
Relational Database Question: Parent that references child
Is it valid to have a parent table have a reference to a child table? For example, a table Parents has a child table Children. Children has a foreign key Parent_ID linking back to its parent. But Parent has another column (say favorite_child_id) that is a foreign key back to the Child table. Is this acceptable or is it bad
How to modify database schema on production
When I start a project with an agile process where I don’t have a complete DER for my database, how I make changes in tables that already have data in production. For example: Have table costumers products and sales, in the beginning sales table only have the columns id, costumerId, productId and quantity. But in the future we need to
Designing a mysql database of relationships between contacts?
I’m trying to design a database of contacts but I also want to keep track of their relationships with other contacts whether it be family, friends, cowokers etc. I created a table for contacts and created an affiliates table that labels the type of relationship but I’m not sure if I did the design correct and I’m unsure if this
How to prevent logical duplicates in a unary one to many SQL relationship table
In a non directional unary relationship what would be the best option to prevent logical duplicates? E.G. Main table ID — 1 2 Relational table ID1 ID2 ———– 1 2 2 1 Entry two …