To store normal operation hours, you would need to store a number of records containing: Shop – INTEGER DayOfWeek – INTEGER (0-6) OpenTime – TIME CloseTime – TIME I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records: … Read more
For config data, I’d use the key/value structure with a row per configuration entry. You’re likely to read this data once and cache it, so performance isn’t an issue. As you point out, adding columns each time the set of config keys changes requires a lot more maintenance. SQL excels at modeling and manipulating arbitrarily … Read more
The way MySQL implements the TIMESTAMP data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP column with a default of CURRENT_TIMESTAMP and apply the UNIX_TIMESTAMP() to it if you want to display it as an int: CREATE TABLE foo( created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP … Read more
I don’t know why you’re even trying to compare these to cases. null means that some column is empty/has no value, while default value gives a column some value when we don’t set it directly in query. Maybe some example will be better explanation. Let’s say we’ve member table. Each member has an ID and … Read more
Your senior colleague is a developer, not a data modeller. You are better off starting from scratch, without them. Normalisation is complicated only to those who will not read books. It is fair enough that he makes you think, but some of the issues are absurd. Your numbers: You need to appreciate the differences between … Read more
You should use TEXT like the others said, but there is some important advice every time you use TEXT or BLOB: decouple them form your base table as they really slow down accessing the table. Imagine the following structure: CREATE TABLE article ( id INT(10) UNSIGNED, title VARCHAR(40), author_id INT(10) UNSIGNED, created DATETIME, modified DATETIME … Read more
The question and selected answer are from 2008 and since then this functionality has been integrated into the django framework. Since this is a top google hit for “django filter foreign key count” I’d like to add an easier solution with a recent django version using Aggregation. from django.db.models import Count cats = A.objects.annotate(num_b=Count(‘b’)).filter(num_b__lt=2) In … Read more
The two most common reasons to denormalize are: Performance Ignorance The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper 😉 I would say a better mantra would be “normalize for correctness, denormalize for speed – and only when necessary”
I’ll see if I can do my best to answer your questions succinctly. 1.At what point is building a data warehouse an option worth considering? In other words, what telltale signs, metrics, or other criteria should I be looking out for that might indicate that a standard transactional environment is no longer sufficient? a. If … Read more
Given that the badge criteria can be arbitrarily complex, I don’t think you can store it in a database table broken down into “simple” data elements. Trying to write a “rules engine” that can handle arbitrarily complex criteria is going to take you down the path of basically re-writing all the tools that you have … Read more