In a fairly animated discussion in my team I was made to think what most people like as primary keys. We had the following groups- Int/ BigInt which autoincrement are good enough primary keys. There should be at least 3 columns that make up the primary key. Id, GUID and human readable row identifiers all should be treated differently. ...
It seems like I will be needing transaction with MySQL and I have no idea how should I manage transactions in Mysql with mixed InnoDB/MyISAM tables, It all seems like a huge mess. You might ask why would I ever want to mix the tables together... the anwer is PERFORMANCE. as many developers have noticed, InnoDB tables generally have bad ...
I am trying to find the best way to design the database in order to allow the following scenario: The user is presented with a dropdown list of Universities (for example) The user selects his/her university from the list if it exists If the university does not exist, he should enter his own university in a text box (sort of like Other:...
Is there a database term for "table or view"? In my app, the table name I'm pulling data from is configurable using the configuration setting UserTableName Now the DBA went and renamed the table but created a view using the original name. So nothing changed on my side but now I feel that UserTableName is misleading and I would like to ch...
I am looking to make a website and in the future desktop apps as well and i know nothing about db design so what tutorials or books (online preferably) are there? thanks ...
I searched in google for this without a good result. The only topic I found in the cakephp trac, was closed without a "real" explanation. Since CakePhp is like one of the rails ports for php and rails does support this. I would like to know why wont be sopport to this feature. ...
I want to take advantage of the new partitioning in MySQL 5.1 but using a standard ActiveRecord model. The problem I have encountered is that to use the partitioning the primary key on the table must include the columns used in the partitioning function. (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys...
Hi, I am trying to process a dimension using SQL Server 2005 Analysis Services. This has worked in the past without problems but recently fails. The dimension is hierarchical using 4 columns from a single table (the entire cube uses a single table). The error message received (regardless if I process the entire cube or the dimension, w...
In my database I currently have two tables that are almost identical except for one field. For a quick explanation, with my project, each year businesses submit to me a list of suppliers that they sale to, and also purchase things from. Since this is done on an annual basis, I have a table called "sales" and one called "purchases". ...
What is the best design for a punch in/out table? Would you store the punch in/out in the same table or separate tables? Why? Edit - Hourly employees punch in at the beginning of their shift and punch out at the end of their shift. Hourly - Exempt employees must punch out/in for lunch in addition to the in/out at the beginning/end of...
An upcoming project of mine is considering a design that involves (what I'm calling) "abstract entity references". It's quite a departure from a more common data model design, but it may be necessary to achieve the flexibility we want. I'm wondering if other architects have experience with systems like this and where the caveats are. Th...
I need a table to store the state of a financial transaction. The state of this transaction can be roughly modelled by this class. class FinancialTransaction { Integer txId, Money oldLimit, Money newLimit, Money oldBalance, Money newBalance, Date txDate } class Money { Currency curr, BigDecimal amount ...
I'm designing a database of statistics about the operation of mechanical equipment. Each batch of data will contain hundreds of statistics, so I'm trying to decide whether to create a single table with hundreds of columns or to split it up into multiple tables, each containing related statistics. For example, I could have one table con...
Use case: User 1 uploads 100 company names (e.g. Microsoft, Bank of Sierra) User 2 uploads 100 company names (e.g. The Gap, Uservoice, Microsoft, Inc.) I want User 1's notion of Microsoft and User 2's notion of Microsoft to map to a centrally maintained entity with a unique index for Microsoft. If someone uploads a name which isn't i...
Greetings stackers, I'm trying to come up with the best database schema for an application that lets users create surveys and present them to the public. There are a bunch of "standard" demographic fields that most surveys (but not all) will include, like First Name, Last Name, etc. And of course users can create an unlimited number of ...
I'm in the process of working on a user profile system for a website and am pondering what would be a better (scalable) approach to take. I've come up with two solutions and am looking for either input or perhaps pointers to something I might have missed. The following create table statements are not meant to be executable but are merel...
Do you always lean towards thinking of db schema when starting or planning a new project it or do you go the other way and start designing UI then moving down the stack? Or do you have a different way of developing? Not really an agile/waterfall/specs/stories question just a way of getting a handle on which way people lean when working...
Are there any good tools for visualising a pre-existing database schema? I'm using MySQL if it matters. I'm currently using MySQL Workbench to process an SQL create script dump, but it's clunky, slow and a manual process to drag all the tables about (which would be okay if it wasn't so slow). ...
I asked about Choosing a method to store user profiles the other day and received an interesting response from David Thomas Garcia suggesting I use the Table Module design pattern. It looks like this is probably the direction I want to take. Everything I've turned up with Google seems to be fairly high level discussion, so if anyone coul...
I am facing an application designed to import huge amounts of data into a Microsoft SQL Server 2000 database. The application seems to take an awful long time to complete and I suspect the application design is flawed. Someone asked me to dig into the application to find and fix serious bottlenecks, if any. I would like a structured appr...