This question was asked to me in an interview.can somebody crack it?

See more: (untagged)

Can you design DB for following application? Application ‘expanse planner’
– current functionality only supports adding positive and negative items with
their description. Planned functionality is to be able to filter individual items
by type of record – for example groceries/entertainment. The DB design needs to be
prepared for future extensions.
We expect to have 100thousands of user with 1000 records per user.

What I have tried:

I am not sure of the answer. This is what I tried:

3 tables

customer
---------
id
name

Transaction_Tye
-----------
Trn_type_id
Trn_Type_Name

Transaction
--------------------

Trn_id
id (fk)
Trn_Type_id(fk)
Tran_Description
Trn_Amount
Trn_date

• Enable Table/ Database Compression
• Eliminate index fragmentation each night.
• Update statistics each night, and set Auto Update Statistics for the database to On.
• Back up the transaction log for the database at least once an hour to allow for reuse of the file and limited auto-growth.
• Either conduct full backups nightly, or conduct full backups weekly with nightly differentials.
• Schedule database integrity checks to run weekly because they probably won’t fit into your nightly maintenance window.
• Set all maintenance jobs to send email alerts on completion or failure of each task to more than one person.