Database Schema Design

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
LVL 4
asp_net2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
asp_net2Author Commented:
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
Scott PletcherSenior DBACommented:
>>  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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.