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 wh...
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: Should table names be plural? Should column names be singular? Should I prefix tables or columns? Should I use any case in naming items? Are there any recommended guidelines out ther...
In a database-centric application that is designed for multiple clients, I've always thought it was "better" to use a single database for ALL clients - associating records with proper indexes and keys. In listening to the Stack Overflow podcast, I heard Joel mention that FogBugz uses one database per client (so if there were 1000 client...
In the past I've never been a fan of using triggers on database tables. To me they always represented some "magic" that was going to happen on the database side, far far away from the control of my application code. I also wanted to limit the amount of work the DB had to do, as it's generally a shared resource and I always assumed trigge...
So, you're writing a web application and you have several areas of the site where the user can upload files. My basic working method for this is to store the actual file on the server, and have a database table that connects the stored filename to the record it relates to. My question is this: Should there be a different table for each ...
I'm not quite sure if this is possible, or falls into the category of Pivot tables, but I figured I'd go to the pros to see. I have three basic tables: Card, Property, and CardProperty. Since cards do not have the same properties, and often multiple values for the same property, I decided to use the union table approach to store data in...
I'm leading a project where we'll be recording metrics data. I'd like to retain the data for years. However, I'd also like to keep the primary table from becoming bloated with data that, while necessary for long term trending, isn't required for short term reporting. What is the best strategy for handling this situation? Simply archive ...
I've taught a database design course for a few years now, starting off each time using the Crow's Foot diagram notation for our entity-relationship models. I can't believe anyone seriously uses the Chen notation (especially for attributes), and I don't find the IDEF1X relationship symbols intuitive at all, particularly for students new t...
I am developing a website that will manage data for multiple entities. No data is shared between entities, but they may be owned by the same customer. A customer may want to manage all their entities from a single "dashboard". So should I have one database for everything, or keep the data seperated into individual databases? Is there ...
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 s...
What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a numb...
Does anyone know of a good tool to quickly and easily create a diagram for a database schema? I don't need it to generate SQL to create the schema, I just want to diagram it. My current process involves drawing out the db schema by hand on paper, works great but there has got to be a better way? ...
Suppose that I have a form that contains three 10 fields: field1..field10. I store the form data in one or more database tables, probably using 10 database columns. Now suppose a few months later that I want to add 3 more fields. And in the future I may add/delete fields from this form based on changing requirements. If I have a d...
I have a multi dimensional OLAP cube with a number of dimensions. Some of these dimensions have hierarchies. The users would like to perform 'what-if' analysis on the measures in the cube by changing the hierarchies in the dimensions. For example, they want to know the impact on departmental resource budgets by moving employees between...
I have a new database table I need to create... It logically contains an ID, a name, and a "value." That value field could be either numeric or a character string in nature. I don't think I want to just make the field a varchar, because I also want to be able to query with filters like "WHERE value > 0.5" and such. What's the best way...
We have need for a "rating" system in a project we are working on, similar to the one in SO. However, in ours there are multiple entities that need to be "tagged" with a vote up (only up, never down, like an increment). Sometimes we will need to show all of the entities in order of what is rated highest, regardless of entity type, basica...
Which Database table Schema is more efficient and why? "Users (UserID, UserName, CompamyId)" "Companies (CompamyId, CompanyName)" OR "Users (UserID, UserName)" "Companies (CompamyId, CompanyName)" "UserCompanies (UserID, CompamyId)" Given the fact that user and company have one-to-one relation. ...
We have a requirement in project to store all the revisions(Change History) for the entities in the database. Currently we have 2 designed proposals for this: e.g. for "Employee" Entity Design 1: // Holds Employee Entity "Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)" // Holds the Employee Revisions in Xml. The Re...
I am looking to implement a data-driven wizard with the question tree stored in an Oracle database. What is the best schema to use to make the database portion flexible (i.e. easy to add new paths of questions) without sacrificing too much in terms of performance? ...
There are several types of objects in a system, and each has it's own table in the database. A user should be able to comment on any of them. How would you design the comments table(s)? I can think of a few options: One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc) Several comments tables, one for ea...