Solved

Database Schema Design

Posted on 2014-12-29
3
98 Views
Last Modified: 2015-02-12
Hello Experts,

I'm looking into building my own application that will allow me to enter Customer Information based on Computer Repair work from my clients. I plan on building the application in ASP.NET MVC 5 using either Entity Framework Code First or Database First approach (not sure which method I'm going to use, still deciding) in C#. Before we begin, I will stress that I'm new to MVC and Database Design.

So, I have sketched out in Notepad how I think the Database Schema Design should be laid out but obviously not sure since I'm asking for help here.

Please see the attached file....

Also, please advise me if I need to correct the naming of my table fields. Not sure if there is a standard that I should follow. If so, please advise how I should go about proper naming of fields in my tables.
DatabaseDesign.txt
0
Comment
Question by:asp_net2
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40522458
This is a very broad topic.  But here are some thoughts to get you started.

Take this step by step.  Database design needs to be an orderly process, don't try to jump straight to the end (i.e. tables with index, a la most developers :-) ).

First, decide which Entities (not "tables" yet) you need to keep data about.  Each Entity will become one or more tables later in the process.  At this point use full business-related names, not lots of abbreviations.  

This overall approach will help you with the design of work orders and accounting data (I'd strongly recommend against naming it by the accounting software, name it by its usage instead; i.e. "Invoices" rather than "QuickBooks...").    

Try to explore reasonable possibilities and include them in the design on the front end.  For example, a single "customer" could itself be a business that has many people in it, lots of whom you do repairs for.  You don't want a design that excludes such a possibility, obviously, esp. since this could be a very profitable type of client.

Then decide which attributes ("columns") you need to keep for each Entity.  This will take some time to flesh out.  Again, use fuller names not abbreviations.  Use IDs rather than natural keys only if you need to.

Next, determine keys of Entities and logical relationships between Entities ("has a", "is a", "orders", etc.).  Also, determine cardinality as best you can: 1-1, 1-many, many-many, etc..

Then -- and only then -- convert to tables as you normalize the data.  Go thru each normalization level one at a time.  Go thru at least 3rd normal form, and bcnf if you want a more robust design.

On the physical side, use ids as identifying column when needed -- customers is one obvious example -- and use combined keys for relationship tables, for example, for customer addresses (personally I would always separate the customer address to a separate table, as inevitably you will have some customers that will need more than one address: billing vs shipping, home vs work, etc..)
0
 
LVL 4

Author Comment

by:asp_net2
ID: 40522903
Hi ScottPletcher,

>> At this point use full business-related names, not lots of abbreviations
Can you explain better to me what you mean, sorry.

>> Again, use fuller names not abbreviations.  Use IDs rather than natural keys only if you need to
Can you explain better to me what you mean, sorry.

>> Next, determine keys of Entities and logical relationships between Entities ("has a", "is a", "orders", etc.).  
Can you explain better to me what you mean, sorry.

>> Then -- and only then -- convert to tables as you normalize the data.  Go thru each normalization level one at a time.
Can you explain better to me what you mean, sorry.

>> On the physical side, use ids as identifying column when needed
Can you explain better to me what you mean, sorry.

Also, as for Customer Address. I plan on updating the data as my clients may move. I will be checking each time I work with them. So if I work with John Doe today and then a month from now if I have a different address then I will update that in the database.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40523720
>>  At this point use full business-related names, not lots of abbreviations
Can you explain better to me what you mean, sorry. <<

Rather than "cusID", use "Customer ID".  Instead of "stID", use "State ID".  You are still in the logical design phase, where purely business people, with no computer background, should be able to read and contribute to the data entities and attributes.


As to the rest, you'll have to do some reading on logical and physical db design.  There is no way I can do a full background for that in the context of a single q (or, in this case, a list of qs).
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question