Jump to content

dB Qs


Cat

Recommended Posts

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

Link to comment

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.

Link to comment

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.

Link to comment

+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 :P ). It's how you design the "schema". I've used MySQL Workbench in the past and its pretty sweet (for MySQL).

Link to comment

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!

Link to comment

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 :P ). 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.

Link to comment

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. :P

Link to comment

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...

Link to comment

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.

Link to comment

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?

Link to comment

[...] 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.

Link to comment

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.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.