0

I am designing a small database with relatively small data. The question is about normalization of Datetime columns. The dates of year occured many times and should I create a Days tables with a Primary Key or let it occure in tgw Main table ignoring Norm rules. (Please ignore the first column named City, see it as CityID)

  • The main table will be truncated once a month and only the datas for the next month will be stored per City.
  • The db schema highly robust so will not change. Also there is no need to add a table in a relation with Date column or Dates table.

    +--------+------------+------+---------+-------+------+---------+------+
    | City   | Date       | Fajr | Sunrise | Dhuhr | Asr  | Maghrib | Isha |
    +--------+------------+------+---------+-------+------+---------+------+
    | Adana  | 01.01.2015 | 3:49 | 5:31    | 12:36 | 4:25 | 7:39    | 9:15 |
    | Ankara | 01.01.2015 | 3:45 | 5:34    | 12:45 | 4:40 | 7:56    | 9:39 |
    | Konya  | 01.01.2015 | 3:57 | 5:41    | 12:47 | 4:38 | 7:52    | 9:30 |
    | Adana  | 13.01.2015 | 3:49 | 5:31    | 12:36 | 4:25 | 7:39    | 9:15 |
    | Ankara | 13.01.2015 | 3:45 | 5:34    | 12:45 | 4:40 | 7:56    | 9:39 |
    | Konya  | 13.01.2015 | 3:57 | 5:41    | 12:47 | 4:38 | 7:52    | 9:30 |
    +--------+------------+------+---------+-------+------+---------+------+
    

You see the dates are occuring. Should the new design is like this?

+-------+------------+
| DayID | Date       |
+-------+------------+
| 1001  | 01.01.2015 |
| 5003  | 13.01.2015 |
+-------+------------+

+--------+-------+------+---------+-------+------+---------+------+
| City   | DayID | Fajr | Sunrise | Dhuhr | Asr  | Maghrib | Isha |
+--------+-------+------+---------+-------+------+---------+------+
| Adana  | 1001  | 3:49 | 5:31    | 12:36 | 4:25 | 7:39    | 9:15 |
| Ankara | 1001  | 3:45 | 5:34    | 12:45 | 4:40 | 7:56    | 9:39 |
| Konya  | 1001  | 3:57 | 5:41    | 12:47 | 4:38 | 7:52    | 9:30 |
| Adana  | 5003  | 3:49 | 5:31    | 12:36 | 4:25 | 7:39    | 9:15 |
| Ankara | 5003  | 3:45 | 5:34    | 12:45 | 4:40 | 7:56    | 9:39 |
| Konya  | 5003  | 3:57 | 5:41    | 12:47 | 4:38 | 7:52    | 9:30 |
+--------+-------+------+---------+-------+------+---------+------+
1
  • 4
    No, it is not needed. And no, replacing values with surrogate numbers is not normalization. May 14 '15 at 20:25
1

Normalization for tiny applications in my opinion is overblown and not needed in my humble opinion and experience. I would focus on customer experience, scalability, and ease of management of code. If your code will scale to meet your customers needs, is performant, and you're happy with managing it then it's good enough. Of course things change when you work in a team environment and have agreed upon or understood standards, then those should be followed or at least going with an 'anti-pattern' should be discussed before implementing.

Also consider the cost of joins. Joins cost CPU time, many DB solutions are licensed by CPU, and they have a management cost. There's a table I manage which has about 12 joins to it. It is heavily used, and it is only 7 MB holding only 15,000 values, but they are critical and often looked up values. I would denormalize this if I could redo it. Writing code for it is a pain.

There are also entire database solutions that are designed to be schemaless, with a popular one being MongoDB. Yes, you give up ACID compliance outside of the document level but it does support really fast queries and is built with denormalization in mind.

Also if you want check out: http://sqlmag.com/business-intelligence/responsible-denormalization

2
  • Thanks Ali, I was exactly wondering about cost of joins and if it is worth to normalize the date column.
    – guneysus
    May 14 '15 at 20:40
  • Anytime, let us know if you have any other questions. Have fun. May 14 '15 at 23:14
2

There is not a real need to have a dates table. However, there are some cool things you can do with a dates table such as add columns to that table that have different pieces of information about that day such as holidays or weekends or week of the year, etc. Instead of doing a function to get that information.

2
  • Thanks Chris but I will not any more column/feature to db or new related tables to Date column. That's why, the question is posted here.
    – guneysus
    May 14 '15 at 20:37
  • I updated my question that my db does not need an additional table but I will keep in mind your holidays suggestion. I may want a feature like this.
    – guneysus
    May 14 '15 at 22:42
2

Consider what the second design implies. To me it implies OLTP operations such as

update Days
set Date = '02.02.2015'
where DayID = 1001;

This is almost certainly not what you want in an OLTP application.

Or in a data warehouse type application it implies that Days is a dimension, and Main is a fact table. However you would normally see a hierarchy in a dimension, which you don't have. So then there's no advantage in not using the built-in datatype instead of inventing a surrogate.

2
  • You are right. I know that the second design it looks weird but this dates are static, will be created once and will not be updated. Also the main table will be truncated once a month and only the datas next for 30 days will be stored.
    – guneysus
    May 14 '15 at 22:32
  • Odd that I am working through a problem with Slowly Changing Dimensions. For a distraction I wondered about denormalizing date/time's and landed here, and you are discussing Dimensions and Fact tables. I can't get away from it...
    – user141074
    Mar 20 '19 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.