how to take table schema design decisions


  • Hi All,

    This is a general question regarding database design. I am not a db designer nor an architect. I am sql/ETL developer.
    However, curios to know about this as some of such related work will be falling in my bucket.Looking for some guidance.

    Assume that we have existing Data model. As and when new requirements comes, we keep adding new tables or adding columns to existing tables and work in Sprint model.
    Similarly, we got some new requirements and now the question arises, can we add additional columns to the existing table or consider creating a new table with same structure with new columns as per requirement(if any).

    What type of questions do I need to ask myself/architect's/business users/dba's if I want to store data in the same table or else consider creating a new table to created with similar structure for new requirements. Is there any thing to be done with capacity planning??

    So, If I have some existing model and is currently meeting business requirements and tomorrow if I get a similar kind of functionality requirement, why can't we fit into the same table and why someone would prefer having a separate table(s) instead of using the existing db structures/tables.

    Basically, I am looking for pros & cons of having data stored in the same table or else is it good to have my data in a separate table for the new similar kind of business requirement? Doing so (i.e. having a new table), Does I/o changes ? locking/blocking/deadlocks gets eliminated? any performance gains of having data in separate table like query performance gains, any database growth considerations need to be considered (if so what questions to be asked to bushiness users)?

    So, as a whole, what are the things to be considered to have an existing table and storing data into same or creating a new table for the new requirement. Which is better??

    FYI its a SQL Azure PaaS db with one .mdf and one .ldf file.

    Kindly advice. Please don't say. it depends or hire a consultant. I am really looking for some generous inputs so that I can involve the right people on this. Right now, everything is in AIR.



    Thursday, July 12, 2018 10:34 AM