What's the difference between identifying and non-identifying relationships?

I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?

MongoDB vs. Cassandra [closed]

I am evaluating what might be the best migration option.

Currently, I am on a sharded MySQL (horizontal partition), with most of my data stored in JSON blobs. I do not have any complex SQL queries (already migrated away after since I partitioned my db).

Right now, it seems like both MongoDB and Cassandra would be likely options. My situation:

• Lots of reads in every query, less regular writes
• Not worried about "massive" scalability
• More concerned about simple setup, maintenance and code
• Minimize hardware/server cost

Database, Table and Column Naming Conventions? [closed]

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:

1. Should table names be plural?
2. Should column names be singular?
3. Should I prefix tables or columns?
4. Should I use any case in naming items?

Are there any recommended guidelines out there for naming items in a database?

What is the ideal data type to use when storing latitude / longitudes in a MySQL database?

Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?

Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Recommended SQL database design for tags or tagging [closed]

I've heard of a few ways to implement tagging; using a mapping table between TagID and ItemID (makes sense to me, but does it scale?), adding a fixed number of possible TagID columns to ItemID (seems like a bad idea), Keeping tags in a text column that's comma separated (sounds crazy but could work). I've even heard someone recommend a sparse matrix, but then how do the tag names grow gracefully?

Am I missing a best practice for tags?

What should every developer know about databases? [closed]

Whether we like it or not, many if not most of us developers either regularly work with databases or may have to work with one someday. And considering the amount of misuse and abuse in the wild, and the volume of database-related questions that come up every day, it's fair to say that there are certain concepts that developers should know - even if they don't design or work with databases today. So:

What are the important concepts that developers and other software professionals ought to know about databases?

Guidelines for Responses:

Keep your list short.
One concept per answer is best.

Be specific.
"Data modelling" may be an important skill, but what does that mean precisely?

Explain your rationale.
Why is your concept important? Don't just say "use indexes." Don't fall into "best practices." Convince your audience to go learn more.

Upvote answers you agree with.
Read other people's answers first. One high-ranked answer is a more effective statement than two low-ranked ones. If you have more to add, either add a comment or reference the original.

Don't downvote something just because it doesn't apply to you personally.
We all work in different domains. The objective here is to provide direction for database novices to gain a well-founded, well-rounded understanding of database design and database-driven development, not to compete for the title of most-important.

First-time database design: am I overengineering?

Background

I'm a first year CS student and I work part time for my dad's small business. I don't have any experience in real world application development. I have written scripts in Python, some coursework in C, but nothing like this.

My dad has a small training business and currently all classes are scheduled, recorded and followed up via an external web application. There is an export/"reports" feature but it is very generic and we need specific reports. We don't have access to the actual database to run the queries. I've been asked to set up a custom reporting system.

My idea is to create the generic CSV exports and import (probably with Python) them into a MySQL database hosted in the office every night, from where I can run the specific queries that are needed. I don't have experience in databases but understand the very basics. I've read a little about database creation and normal forms.

We may start having international clients soon, so I want the database to not explode if/when that happens. We also currently have a couple big corporations as clients, with different divisions (e.g. ACME parent company, ACME healthcare division, ACME bodycare division)

The schema I have come up with is the following:

1. From the client perspective:
   • Clients is the main table
   • Clients are linked to the department they work for
     • Departments can be scattered around a country: HR in London, Marketing in Swansea, etc.
     • Departments are linked to the division of a company
   • Divisions are linked to the parent company
2. From the classes perspective:
   • Sessions is the main table
     • A teacher is linked to each session
     • A statusid is given to each session. E.g. 0 - Completed, 1 - Cancelled
     • Sessions are grouped into "packs" of an arbitrary size
   • Each packs is assigned to a client

I "designed" (more like scribbled) the schema on a piece of paper, trying to keep it normalised to the 3rd form. I then plugged it into MySQL Workbench and it made it all pretty for me:
(Click here for full-sized graphic)

Example queries I'll be running

• Which clients with credit still left are inactive (those without a class scheduled in the future)
• What is the attendance rate per client/department/division (measured by the status id in each session)
• How many classes has a teacher had in a month
• Flag clients who have low attendance rate
• Custom reports for HR departments with attendance rates of people in their division

Question(s)

• Is this overengineered or am I headed the right way?
• Will the need to join multiple tables for most queries result in a big performance hit?
• I have added a 'lastsession' column to clients, as it is probably going to be a common query. Is this a good idea or should I keep the database strictly normalised?

Thanks for your time

Auto Generate Database Diagram MySQL [closed]

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a MySQL database? Preferably it would allow me to edit the diagram afterward since none of the foreign keys are set...

Here is what I am picturing diagram-wise (please excuse the horrible data design, I didn't design it. Let's focus on the diagram concept and not on the actual data it represents for this example ;) ):

see full size diagram