A source of confusion may be the use of the word in two different contexts – data modelling and database query optimization. In data modelling terms, cardinality is how one table relates to another. 1-1 (one row in table A relates to one row in tableB) 1-Many (one row in table A relates to many … Read more
You’ll definitely need to optimize for the queries you’re doing. Here’s my best guess based on your description. You’ll probably want to know all Credit Cards for each Customer, so keep an array of those within the Customer Object. You’ll also probably want to have a Customer reference for each Payment. This will keep the … Read more
A primary key is a logical concept – it’s the unique identifier for a row in a table. As such, it has a bunch of attributes – it may not be null, and it must be unique. Of course, as you’re likely to be searching for records by their unique identifier a lot, it would … Read more
Use the information_schema.table_constraints table to get the names of the constraints defined on each table: select * from information_schema.table_constraints where constraint_schema=”YOUR_DB” Use the information_schema.key_column_usage table to get the fields in each one of those constraints: select * from information_schema.key_column_usage where constraint_schema=”YOUR_DB” If instead you are talking about foreign key constraints, use information_schema.referential_constraints: select * from … Read more
I would use this model Your clients Client —— Client ID Name … Your plans (you can define new plans when you want). I add a Price_per_year if you want to propose a discount if the client buys 12 months in one shot (but it’s only an idea). Plan —— Plan ID Name Credits_per_month Price_per_month … Read more
All as varchar (they aren’t numbers but “collections of digits”) Country + area + number separately Not all countries have area code (eg Malta where I am) Some countries drop the leading zero from the area code when dialling internal (eg UK) Format in the client code
The maximum length of an email address is 254 characters. Every email address is composed of two parts. The local part that comes before the ‘@’ sign, and the domain part that follows it. In “user@example.com”, the local part is “user”, and the domain part is “example.com”. The local part must not exceed 64 characters … Read more
Your question almost spells the SQL for this: DELETE FROM table WHERE id IN (1, 4, 6, 7)
You found the shorthand to set privileges for all existing tables in the given schema. The manual clarifies: (but note that ALL TABLES is considered to include views and foreign tables). Bold emphasis mine. serial columns are implemented with nextval() on a sequence as column default and, quoting the manual: For sequences, this privilege allows … Read more
Normalization is basically to design a database schema such that duplicate and redundant data is avoided. If the same information is repeated in multiple places in the database, there is the risk that it is updated in one place but not the other, leading to data corruption. There is a number of normalization levels from … Read more