0

Currently I am developing a time machine for a open-source Business Intelligence software from scratch using PHP/MySQL.

My time-machine table is used by all other tables that need date info (such as orders, products, etc.) and they binding with time_id. So its MySQL table like this:

 time_id |  timestamp   | day  |  week  |  month  |  quarter
 1          1303689654    25      17       4         2
 2          1303813842    26      17       4         2
 ...

Order table binding like this:

order_id    |  time_id  ...
3123           2
...

edit: it's similar to STAR SCHEMA.

The problem is getting TIME (13:45) information as well. Usually I don't need this, but like orders, and sometimes a couple of tables need this HOUR/MINUTE infomation.

How can I solve this problem cleverly? I have a couple of solutions, but first i want to see your opinions..

kuzey beytar
  • 3,076
  • 6
  • 37
  • 46
  • 1
    Looks like a design flaw. making all other tables dependant on a time keeping table.. Do yourself a favor and replace time_id column with a simple timestamp. and add logic that parses that information to the individual components that you need. – netbrain May 03 '11 at 08:17
  • I don't understand the purpose of this. Why not just use a `DATETIME` field? You can extract all the other info (day, week, month...) from that. – Pekka May 03 '11 at 08:18
  • @netbrain - I use this table for a real-time analysis (BI). So it's like start schema. So I have to do this. – kuzey beytar May 03 '11 at 08:22
  • @Pekka - it's similar to star schema (http://en.wikipedia.org/wiki/Star_schema) – kuzey beytar May 03 '11 at 08:25
  • 1
    @dino beytar: I think you don't get it, a date is just that, a date, not an object you would reference in a star schema. A starting point for a star schema would be the number of units sold on a specific date where the date is a timestamp or a datetime, and you store number of units and product id, which will in turn reference your products table, etc. – wimvds May 03 '11 at 08:55
  • +1 @wimvds - I know, you right. I just gave an example. It's not exactly a star schema; more than star schema. Fact tables already use it. – kuzey beytar May 03 '11 at 09:01

2 Answers2

2

Why don't you simply store timestamps in your other tables?

Or, if you want to keep the dates table, simply add a TIME field to your other tables which need it.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • +1 According to your solution, I don't need `TIMESTAMP` in my time-machine table anymore. I mean, I can use `Ymd` like (20110403) instead of TIMESTAMP. Because if I need time, I can get it from other tables. Thank you... – kuzey beytar May 03 '11 at 08:32
0

If I understand your timestamp correctly, you can probably just go:

echo date('H:i.s',1303689654);

edit

What are you trying to do? After re-reading your question you may be looking for the JOIN keyword in SQL (brief tute)

Ben
  • 54,723
  • 49
  • 178
  • 224
  • The problem is if you have more than one TIME in a day, so you have to create more than one timestamp for a day. But I shouldn't create more than one time for a day. So far, ThiefMaster's solution solve this problem. – kuzey beytar May 03 '11 at 08:19
  • 1
    Oh ok. Typically timestamps are unique to an individual operation, so ThiefMaster's answer is quite appropriate. +1 – Ben May 03 '11 at 08:22