I've been given a stack of data where a particular value has been collected sometimes as a date (YYYY-MM-DD) and sometimes as just a year. Depending on how you look at it, this is either a variance in type or margin of error. This is a subprime situation, but I can't afford to recover or discard any data. What's the optimal (eg. least...
Hey there, I'm building an application (using the zend framework) where we will have multiple clients who login and use the application, and each of these clients will be storing lots of data about their users (I'm using MySQL btw). Basically I'm wondering 2 things: Is having multiple databases, one for each client (ie. ipd_client_CL...
I feel like I hit a wall trying to structure what I thought would be a simple database schema for modeling basic financial transactions. I'm hoping some of you with more experience can weigh in and point me in the right direction. My application has four distinctly different types of leases that customers can purchase. As such, each typ...
Facebook has cooked into their search some features that are unique -- possibly some are patented, even? The features I speak of are driven by three distinct requirements: The fact that their database is gigantic, and they can't just JOIN their way over to the data they need as they need it, as you can, typically in a single-homed busi...
I have a site where people can add their favorite TV shows. I would like to have some trends statistics. Example: (1 unchanged) The Big Bang Theory (3rd last week) How I Met Your Mother (2nd last week) House (30th last week, up 400%) Nikita I'm not sure how to design the database for this, but here is my idea: Once a week, I run ...
Simplified case: Storage Users click on a link link_clicks +1 for each click Super user sets a multiplier parameter for each click link_reward (+1 * param) for each click User ID is also recorded for each click Retrieval Queries must be done on a specific date range (e.g., "How many click between Oct 10 and Oct 23 for User ID = 4"...
Restaurants have buffets. and Buffets have different spreads. i wanna enable photo and comments on each dish on the Buffet. not sure which is the best way to store them in Database What i have done ? I have a Restaurant Db that stores the general information of the REst. Dishes DB that stores Dishes of the Resturants. each dish is aga...
What is the diffrence between a unique index and a unique key? ...
A book has several chapters, and each chapter has several sections. This could be deemed as either a two-level tree or a nested list. Now I need to display the catalogue of the book. And the user can drag-and-drop to re-order chapters and sections. Do you know any good javascript widget for this? After the re-ordering, the new orde...
Practicing web development by making a social network. Users can earn points for performing certain activities, so I have a PointHistory table for each User where point transactions are stored. To calculate the point balance, it makes more sense to have a dedicated PointBalance table that gets updated every time there is a change in a ...
Hello I'm about to create a school management system where it should assure the relashionship between teachers, courses, classes, parents, students, report card, enrollment, administration, attendence ... Some good ideas on the DB structure and the system will be appreciated. ...
I've started to develop my own mini project in restaurant reservation to improve my Java skills. At the moment, I create a table consists of number of tables in the restaurant (column) and time interval during the day (row). So for example, if someone booked a table at 11:00 on Table 4, that cell will become reserved and mark as b...
Hi there! My application needs two sets of data, and I want to separate it logically on two parts. It's ideal to have namespaces for tables in one instance of mysql database, but I can't find any clues that this feature exists. So, I decided to split data onto two schemas (static & dynamic) in one instance of mysql. Though, my dynamic da...
I have a tree like graphs and I need to store them into database. I am looking for information how to efficiently store and query graphs in SQL database. ...
I'm building a PHP/MySQL website and I'm currently working on my database design. I do have some database and MySQL experience, but I've never structured a database from scratch for a real world application which hopefully is going to get some good traffic, so I'd love to hear advices from people who've already done it, in order to avoid...
Hello guys, I am currently developing a database via MS Access 2003 and got stuck at a circular reference problem. Basically, it comes down to the following relationship triangle (it is a simplified form of my relationship table): Positions oo oo / \ ...
Is email address a bad candidate for primary when compared to auto incrementing numbers. Our web application needs the email address to be unique in the system. So, I thought of using email address as primary key. But, my colleague suggests that string comparison will be slower to integer comparison. Is it a valid reason to not use ema...
Lets say you are dealing with your normal contact database (you know... name, phone number, address, email, etc...). If you're concerened about this locally, it's generally not a big issue to deal with, but when we look at international sets it is. Looking at the phone number system, you would think it's simple, but it's really not. In ...
I'm trying to determine how to best design a storage facility for fast searching of text. There will be a different file format for each customer These files are XML, and the field names and attributes are not standard, and do not follow a schema The customer has an option to choose certain fields to be searchable There could be 100,00...
Hi guys, I like database design and I'm currently developing one in SQL Server 2008, one thing I dislike is making all the simple procedures like 'person_exist' or 'get_person_data'. How do you handle functions and procedures involving database data? am I doing right? ...