In an invoicing application, consider following: I have a products table that also contains the price of the product. And then I have an invoice and invoice_lines table and in each invoice line, I refer to the product id along with quantity. In this case, I am not storing the price of the product with the invoice line. Now few months l...
I'm working on a personal project problem. It's a donation management model. I need to link donations to entities. For example, the original set of entities that donate are Families, Companies, and Individuals. I want to link donations to these entities and interrelate entities to each other. Is there an example of designing this mo...
How should columns in a database table that have a PK/FK relationship be named? Should their names contain a reference to the table they are in? Consider the examples below. Each employee has an ID which is a FK in the salaries table. /* Employees Option 1*/ CREATE TABLE dbo.Employees ( EmployeeID INT PRIMARY KEY, EmployeeFir...
Over the years I have read a lot of people's opinions on how to get better performance out of their SQL (Microsoft SQL Server, just so we are all on the same page...) queries. However, they all seem to be tightly tied to either a high-performance OLTP setup or a data warehouse OLAP setup (cubes-galore...). However, my situation today is ...
Question: I'm planning the database for one of my programs at the moment. I intend to use ASP.NET MVC for the user backend, the database being on Linux and/or on Windows. Now, even if I would only make it for windows, I had to take into account, that different customers use different database systems. Now, I figured I use nHibernate, t...
Is there some performance loss if one of tables in my database has huge amount of columns? Let's say I have a table with 30 columns. Should I consider splitting the table into few smaller ones or is it ok? What is a recommended maximum amount of columns per database table? Thank you. ...
I'm trying to improve the knowledge management of MySQL structures available on a project. By structure, I mean tables, views, functions, procedures, triggers. All these structures are extracted in .sql files. I'm looking for a way to document these structures, presenting results à la doxygen in HTML files. For example, I want this too...
Can you see anything that might have been left out for a simple bug tracking system? Here's an updated version with new changes ...
Hi All, I am confused about how to set this up using Cake's conventions. I have a table of users (pretty standard: id (pk, auto-increment), username, password), a table of groups (also pretty standard: id, name, user_id) and a practice table (like a doctor's practice, not like "practice makes perfect"). The practice table will be owne...
Is there some limit on the maximum total amount of data that can be stored in a single table in Oracle? I think there shouldn't be because tables are anyways stored as a set of rows and rows can be chained as well. Does such a limit exist? ...
I'm building an app where each user can have multiple profiles. Users can upload multiple photos for each profile, etc. The folder structure will be something like public_html/ upload/ user-123/ profile-199/ profile-321/ images/ 123423.png I'm going to have a one to many relationship be...
Sometime back while designing something that included user management, I was required to have created_by and last_updated_by columns in the User table. To me it seemed a good idea to have a 1:1 relationship on User itself as it would serve as an additional check. The obvious issue is creating the first user who will have to create himse...
I'm working on an ASP.NET4.0/C# application for a public site that needs to authenticate only the employees that work at the associated business. The idea is for the site to have a CMS such that employees can go in and make changes to certain content without having to work with any html. My question relates to the design and use of a AS...
I have been playing around with Visual Studio 2010 over the past few days working with their SQL Template. I have to say...BOOOOOOOO...not working. Now I have had more fun with xsd's in Visual Studio's C#. I expected database design to be that easy or better but no such luck. Are there any applications out there that you would recommend...
I have a model that already has a couple dozen of columns that will be filled most of the time. Now I need to add fields that might be different each time. what's the best approach? I don't like the EAV pattern. I don't like the idea of having a sparse table either, especially considering how these extra properties could be very differe...
We are starting a new BI project in our company. We have at least three developers working with database design and development. Our tools include Sparks EA, SQL Server 2008 EE and undetermined reporting tools. What kind of tools one can use for database version control in SQL Server? What kind of version control systems there are availa...
Hello, I am currently developing an HR module for a company. Now, I got stuck designing the leave system. Particularly I got stuck at the carrying balances forward Well, the leave system is quite simple at this point, each employee has a 30 days leave per year (starting from the hiring date). The leftover days are carried forward to th...
We have BigTable from Google, Hadoop, actively contributed by Yahoo, Dynamo from Amazon all aiming towards one common goal - making data management as scalable as possible. By scalability what I understand is that the cost of the usage should not go up drastically when the size of data increases. RDBMS's are slow when the amount of...
I need to be able to store something like this: where the green is a template type, the gray is a field container type, and the white is field. (That being, a field has a label and some text, both mediumtext for simplicity; and a field container can store other field containers or text, and a template is a top-level field.) Now, let's...
I have the necessity for a client application to "lock" (aka "check-out" certain business entities store in the database. The workflow is such that: The user navigates to a page for some business object. The user hits "edit". This locks the item from being edited by anyone else. Other users on other workstations will see a little "loc...