About primary key

[Jarmok]

Hi all,
i have a table, it will store millions of rows, corresponding to raw byte sequences from many sources. I´m thinking on using for primary key two options:

1- Columns: (source_id, date, data) and PK = (source_id, date)
2- Columns: (id, date, data) and PK = (id)

The sources are, of course, entries in another table so source_id is FK.

Is there any inconvenience on using a date as part of the PK?

Thanks in advance

[darkagn]

The way you have to think about PKs is how are the records in this table unique? Your first option says that each source will have one record on a given day (is this true?). The second one says every record is unique which of course is true but doesn't really say much about the record. Without really knowing what you are doing, I would suggest doing the following:
Columns: id, source_id, date, data.
PK: id, source_id
This allows each source to maintain a list of records within your table. Adding date to the PK is only useful if the records are individual on any day.

[Jarmok]

Thanks for answering.

Every source generates new records every 100 ms approx, and we have 5-10 sources active. I was thinking in using PK = (source_id, date ) (date has milliseconds precision) to save the disk space used for storing the ID, because the data volume is high.

May it be better to store each source in a table, so i can use only the time as PK?

Columns: (time, data) x 4 tables and PK = (time)

[bigakis]

Technically there is no problem on using date in the key. If you expect the table to store million of rows, it is better to use only the
id as a primary key.
Remember that the primary key creates a unique index with the same fields as the key. Updates and deletes are affected mostly by complex keys,
as the system have to maintain the index.
If the date will never be updated, and (almost) all your queries use the date as a criteria, only then it is a good choice to include it in the key.