Solved

Database Schema Design

Posted on 2014-12-29
3
96 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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
>>  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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now