How does a coder manage the Database?

1

So here’s the thing. I am a new coder and I am working on my first big
project from scratch. My project is using ASP.NET MVC, Google Maps API,
Entity Framework, with SQL Server. Obviously, JavaScript, jQuery, Bootstrap on the front end.

I got a very early prototype started on my local machine but I’m starting
to run into database design complications. I am already new to coding,
but somehow have managed to figure out MVC and get a Web API going.
However, I am even less experienced than that with databases and
database administration. I know how to add/remove database objects and
set up tables and all that jazz; that’s not a big deal. But I’m having
questions such as:

*What is the best way to associate user accounts in my system with their business’ information…

*I want my users to be able to add/remove products to their business’
inventory table… What is the best way to set up the database to store
each individual product?

*What is a good way to pair the information given to me via business owners with the data I pull from Google API?

*Should I store business inventory items that don’t need a description each as a separate object, or just include one long inventory list string?

*When do I have my application create an entire new database versus just add a new table in an existing database? For example, should user accounts and businesses be in two separate databases?

And more…

These questions are less coding questions and more database
setup/administration questions… Questions I’d love to ask someone who
has made a big modern commercial database before that handles accounts
and inventory and all that. Obviously, coding questions will come up too
but a lot of it is just database design.

So, my real question here is… How do most coders do this? I would
imagine a lot of coders are not DB experts… Should I buy a Database
Administration book and read it? Should I consult an expert? Since this
will be a data-driven application, database setup is important. Thanks!

PS: Please, don’t worry about me being overwhelmed. I mean, if this is
something that will seriously take 6 months of dedicated study, it may
be better left for me to call up someone else… But even though I just
started programming a few months back, I’m actually loving getting into
all of this and it is not intimidating at all. It’s just that a project of this magnitude requires me to fill many roles.

3

@P1xt has some really good information in his post. I’ve also been involved in various large scale web application projects at my current job using the same technologies you’ve described above. A couple of minor changes I’d make to his table schema would be to add an IsActive column to the User Table and an IsDeleted field to the Products Table. Then when you’re displaying the inventory, you can filter it to display only active users/businesses and only their current products without losing the information for previous orders. When a business decides to “delete” a product, you just change the IsDeleted flag and it appears to be removed from their portal yet you maintain the information should they decide to look up past orders.

Due to some of the issues we have had with Entity Framework, another change I’d make is to add a key field to the Inventory Table. I only suggest this due to the way Entity Framework handles composite keys (using multiple fields as the key). We’ve found it to be much easier to maintain doing it this way. I would then add a Unique constraint to the User ID and Product ID fields to help prevent duplicates in the table.

As for your question about whether to place products without a description in a single or separate records, you definitely want each one to be a separate record. It will be much easier to maintain and update the information if each item is its own separate item. You’ll also want to keep the business and inventory information in the same database. It becomes extremely complicated to try to join tables from different databases together. The way we do it where I work is we have different databases for each unrelated project and create different tables for the information we need to store.

1 Like
5

Wow, thanks for that great start you guys both! Very helpful information! Seriously.

Gosh I love this stuff! I definitely feel like I’m a back-end kinda guy for sure. Going to take what I just learned from you guys and dig deeper. I feel like if you really want to be good at back-end you should probably know database design huh? I was thinking about it today and I realized, honestly… That’s all what 90% of the most popular sites are… They are all based on their database. The rest of the application just deals with presenting the data, but they are big data driven applications! I suppose this means that database skills are going to skyrocket in demand.

@P1xt - You have a point about the data-cleansing and quite frankly, I did a lot of drawing up the design for this application last night and discovered that I was trying to get too fancy too early. Frankly, there is no need for my application to pull data from Google in the beginning stages; I can just collect the data via form submission from my clients and then map them with my data using Google maps API. I think that would be a much better start, especially since this is a first real project for me. I suppose we could always add the bells and whistles later on if/when the program becomes more popular. I am definitely one of those guys who has a tendency to think way bigger and way too far ahead compared to how I should be to the point where I then overwhelm myself and never really get started. Putting an end to that for this project and sticking with the basics first!

That said, Your guys’ posts were super helpful because even for the basic, I have to be able to properly set up a relational db.

6

That really fits in with the idea of building your ‘Minimum Viable Product’, so it sounds like you’re on the right track.

8

Yeah, I apologize if I was not clear; I definitely didn’t think my app was going to use big data. I was referring to Amazon, Facebook, Google, IBM, etc…

9

UPDATE: Just found some more gold!!! If anyone else is wanting to learn more about DB design for FREE:

10

Some key points (to me) to know about database design.

  1. Understand the concept of normalization, if you’re building a relationship database, it’s vital.
  2. Keys, both primary and foreign, what are they and when do you need them (and not need them)
  3. What columns and rows represent when you’re talking about relational databases.

Also - not sure how it works but make sure you need a relational database - if your design works better with NoSQL designs, look into it (data is stored as objects, I don’t fully understand it myself).

My first introduction to this entire world (aside from the fact that I used them as a kid in the 80s to catalogue comic books and didn’t even know it) was databases, via FileMaker Pro - and for some reason my mind loved them

So - if you have any other questions i’m more than happy to help out anywhere I can - drop me a direct message.

One more suggestion is that a lot of these frameworks, Angular, Node, Ruby on Rails built an ‘interaction layer’ on top of databases so they do the hard work and you can focus more on the broader things

11

Regarding user accounts - if you are using ASP.NET MVC templates, you can check a box and have the template create user authentication for you. It’s a big chunk of work if you do the authentication/verification yourself. After you pick MVC for your template, you get another dialog that gives you more template options within the MVC category. That’s where you can choose (1) Forms authentication or (2) Windows authentication.

(1) Forms authentication will generate a local database for users inside your project’s App_Data folder. That database has tables for user accounts, it does password hashing or encryption, user permissions, etc. And it makes code files to handle all of that. It’s up to you to decide if you want to keep the tables there in the local database or move them to a sql server. I have maintained small projects where the user athentication tables (user roles, passwords, etc) are in the same database as business data. As people noted above, books or articles on database modeling and normalization will help a lot with database design. It’s a sizeable topic on it’s own. But yeah, you probably want user authentication tables to be in the same database as business logic.

(2) Windows authentication, on the other hand, will attempt to log people on using their windows accounts instead of the database credentials. In that case, you don’t create any user authentication tables at all. I think that just makes you edit a configuration file and your application will ask windows to collect your network password when you log in.

TLDR:
ASP.NET MVC templates can create the user authentication/verification for you saving considerable time and effort.

12

Thanks everyone! So I do have a question about the “key table” or join table:

Inventory Table
| User ID        | Product ID | Purchase Date     |
|----------------|------------|-------------------|
| 828934hJI11u34 | 8923hui211 | 01/01/2016 06:51  |
| 828934hJI11u34 | de98221k31 | 01/01/2016 06:51  |

How do I actually put this into use? In other words, say I have a table called Bars with a listing of Bars (created this as a fun little project), a bunch of attribs and ids… Now I have a table of Beers which has beer attribs and the primary key for the beers as well.

Since bars and beers are a many to many relationship, I gather that I now need to make a third table which has its own primary key, the foreign key for Bars and the foreign key for Beers to pair them up. I get that and I even did a design of my database structure…

But when it comes to programming this in, this is where I’m getting tripped up. How do I now actually use the above scheme to tell my program and my users which bars have which beers and which beers are at which bars? I don’t need help with pulling the data in C#, I just need help with how to actually join this “middleman” table into the equation, if that makes sense.

I’ve seen several theory videos on this now, and again, I can implement this in a design but I’m having a hard time visualizing how the code utilizes the third table to relate the above information together… See my example BarBeer domain class:

  public class BarBeer
    {
        public int Id { get; set; }
        public Bar Bar { get; set; }
        public int BarId { get; set; }
        public Beer Beer { get; set; }
        public int BeerId { get; set; }
    }
}

See also my design (note some names appear slightly different in my code but you get the point):

BarBeers.png1475×740 60.2 KB

13

@TheOnlyRealTodd, which version of Entity Framework are you using and are you using Code First, Database First, or Model First design?

Using Entity Framework, you’ll need to do the queries using Linq queries. A tool that I use to test my queries is LinqPad. It has a free version (without Intellisense) and a paid version (with Intellisense). If you are not familiar with using linq queries, I’d highly recommend finding a tutorial on how they work.

14

@kblock-dev

I’m using Code First Workflow, which I think is why I’m having a slightly harder time visualizing this. Honestly, everything has worked out surprisingly well (I worked on it for like 4 hrs last night and got some stuff going without an error messages; definitely a first in my coding career thus far lol).

I am also using AutoMapper to map my viewmodels and data transfer objects to my domain classes for time and simplicity’s sake.

LINQ is definitely one of my weaknesses because the logic is like opposite of SQL which threw me the hell off (you do the SELECT at the bottom?!?!?). However, I have been using a little LINQ. I feel like it just comes down to me putting each of the LINQ methods into use and I’ll pick them up which luckily I am doing some on this project.

Just having a hard time visualizing how to pair a bar with a beer using this middleman table. I haven’t quite gotten to doing that relationship in the code yet which is why I haven’t posted any code for that, but I’m pretty clueless and anticipate hitting a brick wall when I get there… Currently trying to figure out how to map my form’s viewmodel to two separate database entities using AutoMapper. Made some progress but need to work out a few bugs on that, and that is just a one-to-one relationship! Lol. For the sake of posting some code, this is what I got on that so far but you can probably see there are bugs when it comes to properly handling the address Id… My code checks if the bar exists but it doesn’t figure out what to do if the bar exists but no address and/or vice versa. Frankly, it is broken but that’s just because I need to get up and go have breakfast and come back to it later! :

public ActionResult Save(BarFormViewModel bar)
        {
            
            if (!ModelState.IsValid)
            {
                var viewModel = Mapper.Map<BarFormViewModel, BarFormViewModel>(bar, new BarFormViewModel());
                viewModel.IsNew = false;
                return View("BarForm", viewModel);

            }
            if (bar.Id == 0 && bar.AddressId == 0)
            {

                var newbar = Mapper.Map<BarFormViewModel, Bar>(bar);
                newbar.LastUpdated = DateTime.UtcNow;
                _context.Bars.Add(newbar);
                var addressToAdd = Mapper.Map<BarFormViewModel, Address>(bar);
                _context.Addresses.Add(addressToAdd);

            }
            else
            {
                var barInDb = _context.Bars.Single(b => b.Id == bar.Id);
                var addressInDb = _context.Addresses.Single(a => a.Id == bar.AddressId);
                Mapper.Map<BarFormViewModel, Bar>(bar, barInDb);
                Mapper.Map<BarFormViewModel, Address>(bar, addressInDb);


            }
            _context.SaveChanges();

            return RedirectToAction("Index", "Bar");
        }