dB Qs

[Cat]

So, I need to design and write a database. This database will basically be for tracking multiple types of boxes and components, and their various locations, test results, actions, etc.

I used MS Access a little a zillion years ago -- it looks nothing like what I remember, so I'm in essence starting from scratch. I'm looking for recommendations for books or websites on the fundamentals of both database design and actual coding. I don't even know what flavor of database I want to use, so if anyone has some thoughts on how to go about picking one, that would be helpful, also. I believe the software of choice around here is MS SQL Server, but that dosn't mean that's what I have to use (but it does mean I could probably get some internal help, if necessary).

Cat

[Phillip Brooks]

I think most databases are going to offer the same core functionality. The most important thing is to identify your data relationships and minimizing duplicate data.

When we designed our database long ago, we started by using "Database Design for Mere Mortals" by Michael Hernandez. I see that there is a new edition, I don't know what has changed. I still browse through the old edition once in a while when I question what I'm doing.

If you get your database design right and use the Database Connectivity Toolkit, it shouldn't matter which DB you end up using.

[asbo]

The flavor depends on why you're going with a database. I prefer MySQL over MSSQL (though you make a good point about internal resources). If you don't need network access, SQLite is a fast, portable option. ShaunR actually just released a slick toolkit for working with SQLite. Another limiting factor could be deployment - is this going on RT or just desktop systems?

As for design principle, I learned most of what I know through trial-and-error so I don't have any particular resources to suggest. At least a handful of people on LAVA are knowledge about DB design, so you could always post specific questions about speedbumps you run into.

[ShaunR]

+1 for MySQL if its going to be remote (MySQL is the No 1 solution for web-servers and its open source), You can also download Xampp which comes with MySQL and is a complete web-server so you can set up an industrial grade web-server in about 5 mins on your own PC.

But the real expertise in databases (as Phillip points out) isn't what type you use (although I'd have to be held at gun-point to use MS Access ). It's how you design the "schema". I've used MySQL Workbench in the past and its pretty sweet (for MySQL).

[Cat]

Phillip: Thanks for the book recommendation. That's one of the books I was considering, but when the list of possibilities got too huge, was when I decided to ask for help here. And it comes on the Kindle, too!

asbo: I was looking at SQLite today (mostly because I saw Shaun's toolkit). I didn't realize it doesn't support network access. While I may not need network access at first, if this becomes a widely-used tool, I definitely will.

Shaun: I like the open source aspect of MySQL.

All: Yes! I agree completely that the design is the important part. I have a very large vision for this, and need to figure out how to bite off pieces that are small enough to be doable, but then meld back into the whole when I put it all together. As well as making it easy enough to use that I'm not the only person who can figure out how to use it.

Thanks for your input!

[asbo]

But the real expertise in databases (as Phillip points out) isn't what type you use (although I'd have to be held at gun-point to use MS Access ). It's how you design the "schema". I've used MySQL Workbench in the past and its pretty sweet (for MySQL).

Ooooh, shiny. I need more excuses to work with databases so I can play with all this stuff I'm missing out on.

[jcarmody]

It might be worth considering the schema TestStand uses. It may be a dangerous guess, but I'm guessing that they've put a fair amount of though into normalizing test data. It looks sound to me.

[Cat]

Ooooh, shiny. I need more excuses to work with databases so I can play with all this stuff I'm missing out on.

Yeah, MYSQL Workbench does look pretty kewl! I just need to get to work on this before the PM farms it out to someone who could probably do it all in a day.

[SuperS_5]

+1 for MySQL. I use it a lot, although, I also like SQlite for local work. Database design is the name of the game here, but not too hard to wrap your head around once you nail down your delivables.

[Anders Björk]

Yeah, MYSQL Workbench does look pretty kewl! I just need to get to work on this before the PM farms it out to someone who could probably do it all in a day.

One should not forget that Oracle own Mysql now... postgresql is an other alternative.

[Cat]

One should not forget that Oracle own Mysql now...

Ack. I didn't know that. I'm still waiting to see what Oracle does about service contracts on my ancient Solaris versions. So far, no good news is on the horizon...

Database design is the name of the game here, but not too hard to wrap your head around once you nail down your delivables.

Yes, I've told the PM we need to figure out what kind of outputs we want this database to provide to us before we decide what data to put into it and in what format. So far he's buying into this. I'm keeping my fingers crossed...

[asbo]

One should not forget that Oracle own Mysql now... postgresql is an other alternative.

I've been meaning to learn Postgre for a while now. Do you have much experience with it to contrast with MySQL?

(Sorry to hijack, Cat )

[Cat]

(Sorry to hijack, Cat )

No problem. I do it all the time myself.

[mje]

I prefer MySQL over MSSQL as well. Never tried Postgre, and have been impressed with SQLite, though it's of course local only.

Been at least 6 years since I've done anything with DBs, and never in a LabVIEW context, so take my experience with a grain of aged salt.

I think both the MSSQL/MySQL flavors offer similar package as far as foreign key enforcement, triggers, views, etc. The one thing I found desperately lacking in MSSQL though back in the day was the lack of any ability to directly limit results to subsets. If I wanted to return rows 10001-10050 of a large data set, I had to get creative with my queries or just brute force through the result set. MySQL allowed me to easily specify that I want only those 50 records with a simple LIMIT statement, no fuss, no sorting games or anything.

[Daklu]

It's been over 10 years since I've done anything with databases and back then we were programming in VB6. At the time one advantage MSAccess had over other solutions was that it had a pretty easy ui that allowed users to query data. Do the other solutions offered have good data viewers available?

[jcarmody]

[...] one advantage MSAccess had over other solutions was that it had a pretty easy ui that allowed users to query data. Do the other solutions offered have good data viewers available?

We use Access with tables linked to a MS SQL Server DB.

[ShaunR]

It's been over 10 years since I've done anything with databases and back then we were programming in VB6. At the time one advantage MSAccess had over other solutions was that it had a pretty easy ui that allowed users to query data. Do the other solutions offered have good data viewers available?

I use "phpMyAdmin". But of course it needs to be part of a web-server.

[asbo]

I use "phpMyAdmin". But of course it needs to be part of a web-server.

This is what I've always used, given that I was always using MySQL for PHP web apps. It's a little clunky but definitely functional. I imagine that one of the desktop applications will be more feature-rich and comfortable use.