Hi folks, I have a table of music albums. I have a second table of album art. Unfortunately, not every album has some album art data. As such, i will need to do an OUTER JOIN between music and album art. Assumption: both tables just contain ints and varchars .. no blobs, etc. Question Is it better to merge both tables into one to re...
Hi folks, EDIT1: Tried to clear the question up by renaming the tables AND their relationships. EDIT2: Please don't look at the what TYPE of data i'm holding in the three DB tables. They were made up on the fly. They are NOT my real world scenarios (and no, I can't talk about my real world data .. in fact it's 1 parent and 6 children, c...
I have two simple tables in my database. A "card" table that contains Id, Name, and text of a card, and a "rulings" table which contains the Id of the card, and text detailing the rulings for the card. Often enough in the ruling text, there is a reference to another card in the database. It is easy enough to find this in the text becaus...
For some school groupwork, I'm making a Generic Pizza Delivery Everything Management System, and got stumped on a problem during data modelling that I can't figure out without using at least several layers of ugly. The restaurant keeps stock of ingredients and beverages/snacks/etc. (I'll refer to these as "drinks"). Drinks have a sellin...
I seem to often find myself wanting to store data of more than one type (usually specifically integers and text) in the same column in a MySQL database. I know this is horrible, but the reason it happens is when I'm storing responses that people have made to questions in a questionnaire. Some questions need an integer response, some need...
I currently working on an issue tracker for my company to help them keep track of problems that arise with the network. I am using C# and SQL. Each issue has about twenty things we need to keep track of(status, work loss, who created it, who's working on it, etc). I need to attach a list of teams affected by the issue to each entry in ...
Let's say that I have a record in the database and that both admin and normal users can do updates. Can anyone suggest a good approach/architecture how to version control every change in this table so it's possible to rollback a record to a previous revision. Thanks /Niels ...
Are there any guidelines/best practices for deciding what type of data should be stored in the database? For ex., is it ok to use database to store Application logs Configuration details (like server IP addresses etc.) System information (e.g., names of shell scripts, scheduling information for batch jobs, batch jobs status etc.) I...
I am fairly new to MySQL and have a project in which I need to design a database that will store responses from an online questionnaire. Reports will need to be written from the data. Does anyone have any tips on what type of fields to use? The questions will either have a Yes No answer, a choice of 4 options from very satisfied to ve...
I want to know what is the difference between a query and a view in terms of performance. And if a view is costly, what else besides a query could I do to improve performance? ...
I'm working on a social networking system that will have comments coming from several different locations. One could be friends, one could be events, one could be groups--much like Facebook. What I'm wondering is, from a practical standpoint, what would be the simplest way to write a comments table? Should I do it all in one table and...
To follow on from my question yesterday.... http://stackoverflow.com/questions/323842/mysql-table-design-for-a-questionnaire I sat down with my boss yesterday afternoon to run through how I was proposing to design the database. However, now I am more confused than ever. He has been using Access for many years, and has questioned whet...
I am looking for a full fledged data modeling tool. Some of the features I would like are support for multiple databases, forward/reverse engineering, logical/physical modeling etc. Please suggest one entry per response. ...
Let's say I have a table that represents a super class, students. And then I have N tables that represent subclasses of that object (athletes, musicians, etc). How can I express a constraint such that a student must be modeled in one (not more, not less) subclass? Clarifications regarding comments: This is being maintained manually, n...
I store different items (notes, articles, pictures, files) in a single table (there are many metadata in common for all the item types - for example, categories, tags, rating, statistics etc.). My first design was like this: table Items, plus another "detail" table for each of the item types (NoteItems, ArticleItems, PictureItems etc.)....
How would you model these relationships in a db? You have a Page entity that can contain PageElements. A PageElement can for instance be an Article, or a Picture. An Article table obviously has other members / columns than a Picture. An article could have ie. "Title", "Lead", "Body" columns that are all of type nvarchar, while a Pictur...
I need to add user roles and permission system into my web application built using PHP/MySQL. I want to have this functionality: 1- One root user can create sub-roots, groups, rules and normal users( all privilegis) . 2- Sub-roots can create only rules, permissions and users for his/her own group ( no groups). 3- A user can a...
For those agile practitioners out there... How do you manage changes to a database schema during a project? My assumption is that in an agile project the schema of any database involved will change and be refactored just as happens with the codebase. Is this assumption correct? If so, do you have any particular tools or processes that ...
I am implementing a tagging system on my website similar to one stackoverflow uses, my question is - what is the most effective way to store tags so that they may be searched and filtered? My idea is this: Table: Items Columns: Item_ID, Title, Content Table: Tags Columns: Title, Item_ID Is this too slow? Is there a better way? ...
I know it's probably not the right way to structure a database but does the database perform faster if the data is put in one huge table instead of breaking it up logically in other tables? I want to design and create the database properly using keys to create relational integrity across tables but when quering, is JOIN'ing slower than ...