Need advice for database design RRS feed

  • General discussion

  • Hello,

    I am going to start my first real database project, this project is a learning project as an individual. As some of you suggested to start a database you need to work out a good database structure/design. Here is my initial design.

    My application is a project management application for small production companies. It happens quite often they loose track of documents and their status and this could help them in the follow-up.

    Underneath is my database scheme; any suggestions are more than welcome.

    Friday, June 22, 2018 8:19 AM

All replies

  • Hello,

    Great start, many don't take the time to plan ahead. With that said, the best way to worth through a design to ensure it will have the proper schema is to actually create the database, tables and setup the relations followed by adding data. Once this has been done, write SELECT statements against the data, do you get the proper results? If not change the schema.

    Some suggestions on what has been represented (there are more but this should spur or not ideas).

    • Review the design, can you see where a reference table might be needed e.g. project type (or drawing type or a status such as in dbo.Project), let's say there can be more than one type of project, perhaps a project type name might change over time, if hard coded in a record you need to update all records while using a reference table and relate to the primary key you change the project type in the reference table and done.
    • Each table should have a auto-incrementing int primary key.
    • Primary key should be more than just "id" e.g. ClientId, OffersId etc. that way when building joins you don't have to alias ID from one or more tables and it's clear when reading a SQL statement.
    • Consider constraints, there are many forms to consider from preventing duplicate records to how tables relate and how records are removed (cascading deletes). A sub-topic is how to handle these in code (see my TechNet article with code samples).
    • Consider when a record is not needed to not delete but use a soft-delete which means adding a Active bit/boolean field or a backup table(s).
    • Consider adding a modified date to each table and set the default to today (easy in most databases)
    • Normalize Clients (others may find the need too), for example, what if a client has several email and phone numbers? Below is a simple example for a contact/customer

    • Test your UPDATE and INSERT to the database (SELECT was mentioned above). Never wait until coding to test SQL operations.
    • The statements mentioned above can be saved and used later in your app.
    • Create a development and production database (either on the same server or different servers).
    • Don't use MS-Access as your database, this could easily be a long drawn out discussion by itself.
    • If you select SQL-Server (paid edition on SQL-EXPRESS edition) get SSMS (SQL-Server Management Studio) for working on your database. The image above was done in SSMS.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 22, 2018 9:56 AM
  • Karen has some good suggestions.  Having worked with one of the largest ERP packages (namely Dynamics AX) I would suggest further normalization of some of these tables.

    Clients and staff should be mostly combined into Contacts.  This would contain name fields, job title, and contact type.  Addresses go into their own table and relate to Contacts.  This allows multiple addresses (bill-to, ship-to, etc.) as needed.  Companies is another table related to Addresses and Contacts (a company can have addresses separate from their contacts, for example, the physical company address and the addresses of outside sales reps); the business using the database is also a record in the Company table.  Phone and email can be a little tricky... you might have primary phone/email on Contact and Company and secondary on Address, or Phone and Email might be their own tables with a type identifier related to Contact, Company, and Address.  This is where normalization can get a little tricky as you do not want a lot of redundant data, yet you do not want to perform extra table lookups for commonly needed information.  Ultimately the estimated size of the database and common workflows will help you decide which way to go (more on workflows below).

    Customers and Vendors are each a table related to Company and likely include a primary contact record from Contacts.  This allows for individual customers or contractors not associated with a company, and facilitates situations where one company is both a customer and a vendor.

    Purchase Orders and Sales Orders are typically split into their own tables as one has Customer records and the other Vendor records.  It also better supports general ledger accounting, if you take it that far.

    I realize that you are aiming more for project management than full resource planning, but some of the above considerations may still apply in a general sense.  For example, what if a project spans multiple purchase orders or engineers?

    Another consideration is who will have access to modify records in each table?  Unless it is tightly controlled by only one or two individuals, it may be worth adding Created Date, Created By, Modified Date, and Modified By to each table.  When multiple users can update records, a business often runs into a situation where it wants to know who changed what and when.

    If the database is always expected to be small you can probably get away with nvarchar id fields as you have in Project.  But generally it is better for all tables to use autogenerated int PKs and then have secondary identifiers with an index.  Integer PKs tend to be better for the database engine's relationship management and indexing secondary identifiers is friendlier for users doing queries.  That said, here is where I differ from Karen in that I use "Id" for all autogenerated PKs and then TableNameId for the indexed friendly identifier.  I rarely run into the issue of aliasing a primary key in a join because that information isn't generally useful in the output where a join is relevant.  In my experience, joins are typically for read-only queries like reports.  Workflows that required modifying the database usually rely on a query for each table involved in the workflow.  Please note that this is my opinion and is based on usage scenarios I have run into - Karen's advice may very well be better for another usage scenario.

    Speaking of workflows, that is where I would start before designing the database very far.  A rough sketch of the database can be useful in mapping the workflows, but the workflows are what really determine how a database will be used and thus its optimal structure when there are different acceptable designs.

    Finally, you must choose the database engine to use before designing tables, as that is critical to the design.  Chiefly, are you going to use a traditional relational database (RDBMS), a NoSQL document-based database or NewSQL?  A project management system (or document management system) may be suited for NoSQL, particularly if you anticipate a lot of differences from case-to-case (or cannot anticipate differences) in the layout of the data that needs to be stored.  While big-data probably won't apply to a solution meant for small shops, some of the other considerations may apply in a document management system.  This article and its links may helps shed some light on the differences.

    This is a lot to absorb and consider.  Before being overwhelmed, always remember the KISS principle and then add complexity as needed.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, June 22, 2018 1:03 PM