As a technical lead how to ensure that DB schema is perfect for the new project to start with?

Question

Pardon me if this question seems kind of newbie question or not appropriate. But as a new Technical Lead i'm a bit confused about is my DB schema is perfect.

Working with a small team of 3 to 4 person all are fresh graduates. Only I'm the one with experience of more than 3 years. We are working on new product documentation and schema designing. After 2 weeks of hard work our documentation and schema is complete. Schema is passing and fulfilling all the major functionality we mentioned in the documentation.

My question is how to ensure that there is nothing left behind or handle any scenario which will occur in future?

Answer 1

You don't try to handle everything up front or predict the future. It's a waste of time and effort. As you build out the software and get stakeholder feedback, you will learn more about the true needs of the stakeholders.

I'd recommend looking at the techniques of Agile Data and Agile Modeling. These two sets of practices can help you apply and integrate database engineering and modeling techniques with Agile Software Development methodologies, including balancing your up-front design with product delivery and refactoring at the database/data model level to account for changes.

Answer 2

You don't. Schema can be changed at any point in future. Predicting perfect schema is impossible. I say that after doing this for 6 years now. It is simply impossible to know what can come up.

With that said, you should definitely consider normalisation, adding basic security measures like using guid instead of sequential numbering, option to ensure transaction consistency, option to keep log of data updates and most important, performance. Remember, indexes and views for reading, stored procedure for creating and updating.

And remember, no matter how good you are, you can never predict the future. So, if something went wrong in initial schema, no problem, just think of it as learning opportunity.

Answer 3

Firstly, I'm not so sure this has to do with Project Management.

More to the point, why bother? It's rather simple to add fields to a database - even a live one.

If you're really paranoid, you could always add a few extra fields, to be named spare1, spare2, etc. and use those later.

To answer the actual question of how to ensure that there is nothing left behind or handle any scenario which will occur in future?:

Run through as many real life use cases as you can think of and make sure the DB schema handles everything.

Classic things that come up when it's too late (based in my experience):

• When did it start? I.e. when did user first sign up/when was item first included in the inventory?
• When was the last time it was accessed? I.e. when did the luser last login/backup, or when was the item last sold/bought/etc.
• Nowadays that space is no longer expensive and speed is not an issue, there is no reason not to have a transaction table; one that logs all transactions so that you can figure out every movement of every user/item, etc.
• Multiple (encrypted, of course) password fields to support lost password emails, no-reuse of previous passwords and related rules you may want to enforce.

Answer 4

Strongly normalized data schema should be more flexible to change to meet evolving requirements. Ensure the schema is normalized.

Beyond that, this is a quality/suitability to business requirements problem. Collect and test against your requirements. Rely on your team (although I think the most important phrase in your question is that you are the only one with experience). I wonder if this is an opportunity to do some early skills transfer by getting them to come up with use cases and misuse cases for database queries.

My gut says that the database schema is a symptom of a truly interesting question about skill transfer, training and capability building. Good luck