Database Design – Duplicate Column vs Foreign Key Lookup

database-design

I have a base user profile table that stores data for users. Each user is able to upload multiple pictures so this is represented by a separate photo table.

When the user profile is queried, the entirety of the user's photos are not necessarily required, only the thumbnail of the first photo (profile picture). Only a further action will require the rest of the photos.

I would like to know if it is optimal to store a duplicate of the thumbnail of the profile photo on the user profile table or should I continue to do a complex nested query to fetch the thumbnail of the profile?

Profile

user_id | avatar --(cached copy of profile[0].thumbnail)

Photo

user_id | image | thumbnail | order

Best Answer

Unless your database is inefficient enough to read the whole row when you ask for just the IDs and the thumbnail, then don't duplicate the data. The join or sub-query to add the thumbnail when needed will not be "complex" for any good database system. You have either

SELECT <stuff>
FROM   Profile
LEFT OUTER JOIN
       Photo
       ON  Photo.user_id = Profile.user_id
       AND Photo.order = 0

SELECT <profilestuff>
     , thumbnail = (SELECT thumbnail 
                    FROM   Photos 
                    WHERE  Photo.user_id = Profile.user_id
                    AND    Photo.order = 0
                )
FROM   Profile

You'll want a the Photos table to have an index on user_id, order but you'll probably have that anyway as it is the only candidate in this table layout for the primary key.

In either case you could create a view that does this instead of including the extra lookup in all other queries that reference the profiles and need to know the thumbnail.

If you duplicate the thumbnail you have to implement keeping that cached copy in sync with the other data, and it will be wasteful of space if you are storing the actual binary file not just the filename or other reference.

You might want to add a thumbnail id to the profile table to allow the user to pick an image that isn't the first one (if they have them ordered chronologically for general display but think one from the middle best represents them, for instance).

Related Solutions

MySQL Database Design Help

admin and active is just a bunch of 0's and 1's so can I do something like this? Since there will be 99% active and only a couple that will be inactive.

admin - id, user_id
inactive - id, user_id
social - id, user_id
facebook_link, twitter_link
member_forgot - id, user_id, forgot_code

Actually no, I wouldn't do this and keep it in the same table. The flag should be enough to distinguish between these two. The only difference in your queries is a active = true. Personally, I wouldn't consider this a bad design choice (altough your idea is not wrong, I would not use it in this case with - I'm guessing - a small set of data). Since only on set of data belongs to a single user, JOINs would make not that much sense.

I really want to practice splitting it up in different tables even if it is not a huge database, or is that a bad idea? So if I delete a user from the users table, it will delete all of the other records in the other tables associated with that user_id?

Again, I wouldn't do that personally(!). I don't see a benefit of seperating user centric data from the user. If you fear losing the data on deletion, introduce a deleted flag. This way, you will never lose data and your application just respects the deleted flag in your queries (this would also apply if you split the data accross tables).

Credit_Cards = 1110 format (visa, mastercard, discover, amex)

Storing credit cards is always very sensitive, according to PCI (as far as i remember) you are not allowed to store the CVV code (hope someone can correct me on that). But since this seems to be a fictional application, that should not be a problem.

Sql-server – What to do with duplicate lookup information

This type of thing should be handled by the ETL process that brings the data into the data warehouse. In fact, this process is the T in ETL.

What you need to do first is define the logical key column(s) of the tables, so the business meaning of the rows can be equated between the databases. A multi-column key as you propose would complicate matters, and really doesn't solve the problem.

For this example, I would define CustomerState as the logical key column in the dimension, and when the separate tables are merged together, this column would be unique in the result, with new CustomerStateId values assigned. This ensures the dimension primary key is as narrow as possible, which will carry through to the fact tables and make them as narrow as possible as well.

The ETL process might do something like this (assuming the CustomerStateId column of the target table is an IDENTITY column):

MERGE INTO [dbo].[CustomerState] tgt
    USING [Staging].[CustomerState] src ON src.CustomerState = tgt.CustomerState
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerState) VALUES (src.CustomerState);

(The reason I used MERGE instead of INSERT is that in other dimensions you may need to handle doing updates as well; not in this case as there are no other columns.)

Then, the fact table loading process would use a lookup mechanism (Lookup Transformation in SSIS) to go from the CustomerState logical value to the newly-assigned CustomerStateid value generated by the above statement.

Best Answer

Related Solutions

MySQL Database Design Help

Sql-server – What to do with duplicate lookup information

Related Question