Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Schema Design

Posted on 2014-12-29
3
Medium Priority
?
103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1500 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 70

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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
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.

670 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