Fixed-size array database field

Question

I need to store several date values in a database field. These values will be tied to a "User" such that each user will have their own unique set of these several date values.

I could use a one-to-many relationship here but each user will have exactly 4 date values tied to them so I feel that a one-to-many table would be overkill (in many ways e.g. speed) but if I needed to query against them I would need those 4 values to be in different fields e.g. MyDate1 MyDate2 ... etc. but then the SQL command to fetch it out would have to check for 4 values each time.

So the one-to-many relationship would probably be the best solution, but is there a better/cleaner/faster/whatever another way around? Am I designing it correctly?

The platform is MS SQL 2005 but solution on any platform will do, I'm mostly looking for proper db designing techniques.

EDIT: The 4 fields represent 4 instances of the same thing.

Answer 1

How about having 4 fields alongwith User ID (if you are sure, it wont exceed that)?

Answer 2

If you do it as four separate fields, then you don't have to join. To Save the query syntax from being too horrible, you could write:

SELECT * FROM MyTable WHERE 'DateLiteral' IN (MyDate1, MyDate2, MyDate3, MyDate4);

As mentioned in comments, the IN operator is pretty specific when it comes to date fields (down to the last (milli)second). You can always use date time functions on the subquery, but BETWEEN is unusable:

SELECT * FROM MyTable WHERE date_trunc('hour', 'DateLiteral') 
IN (date_trunc('hour', MyDate1), date_trunc('hour', MyDate2), date_trunc('hour', MyDate3), date_trunc('hour', MyDate4));

Answer 3

Create four date fields and store the dates in the fields. The date fields might be part of your user table, or they might be in some other table joined to the user table in a one-to-one relationship. It's your call.

Answer 4

For what it's worth, the normalized design would be to store the dates as rows in a dependent table.

Storing multiple values in a single column is not a normalized design; normalization explicitly means each column has exactly one value.

You can make sure no more than four rows are inserted into the dependent table this way:

 CREATE TABLE ThisManyDates (n INT PRIMARY KEY);
 INSERT INTO ThisManyDates VALUES (1), (2), (3), (4);

 CREATE TABLE UserDates (
   User_ID INT REFERENCES Users,
   n INT REFERENCES ThisManyDates,
   Date_Value DATE NOT NULL,
   PRIMARY KEY (User_ID, n)
 );

However, this design doesn't allow you make the date values mandatory.

Answer 5

Some databases like Firebird have array datatype, which does exactly what you described. It is declared something like this:

alter table t1 add MyDate[4] date;