I want to write a calendar application. It is really recurring items that throw a wrench in the works for the DB schema. I would love some input on how to organize this. What if a user creates an event, and inputs that it repeats everyone Monday, forever? How could I store all that in the database? I can't create infinite events. Do I s...
I have a normalized database and need to produce web based reports frequently that involve joins across multiple tables. These queries are taking too long, so I'd like to keep the results computed so that I can load pages quickly. There are frequent updates to the tables I am summarising, and I need the summary to reflect all update so f...
To adhere to 1st normal form, one of the things you must avoid is repeating groups. As in instead of: CustID Name Address Phone1 Phone2 Phone3 102 Jerry 234 East.. 555-2342 555-9854 555-2986 You should create a second Telephone Number table and then on a join you would get: CustID Name Ad...
A coworker sketched out the values of a new table as: "Foo", "some value 1" "Foo", "some value 2" "Foo", "some value 3" "Bar", "some value 3" These are the only columns in the table. The column names are Col1, Col2. One person said that this table is not normalized, another said it is. The specific argument that it violated norma...
Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territori...
I need to know the average volume of an mp3 file so that when I convert it to mp3 (at a different bitrate) I can scale the volume too, to normalize it... Therefore I need a command line tool / ruby library that gives me the average volume in dB. ...
To describe my dilemma, let me first start with an example problem (stolen from here). Let's say you have a GradStudent table in your database that looks like this: GradStudent: firstName lastName birthDate courseAssignment researchGrant But only teaching assistants will have a course assignment and only research assistants will have ...
I have always wondered how Facebook designed the friend <-> user relation. I figure the user table is something like this: user_email PK user_id PK password I figure the table with user's data (sex, age etc connected via user email I would assume). How does it connect all the friends to this user? Something like this? user_id...
A SQL VIEW is a global, logical table that may or may not be persisted. But it's still a table. Therefore, should a VIEW always adhere to first normal form (1NF)? i.e. no duplicate rows, scalar types only, no top-to-bottom or left-to-right ordering, etc. What about the higher normal forms? For me, my applications 'consume' the results o...
Let's say you're collecting insider info on upcoming superhero movie releases and your main Movie table looks something like this: Table 1 Title Director Leading Male Leading Female Villain -------------------------------------------------------------------------- Green Lantern Kubrick Robert Redford ...
I'm working on a project for a friend and I've come across a difficult decision. The project consists of essays, each of which can be challenged, and also commented on. The thing is this, only one person is able to challenge the essay, and then everybody else is locked out and can only comment. The rebuttals can only be two responses de...
Hi folks, I've got a database in production for nearly 3 years, on Sql 2008 (was '05, before that). Has been fine, but it isn't very performant. So i'm tweaking the schema and queries to help speed some things up. Also, a score of main tables contain around 1-3 mill rows, per table (to give u a estimate on sizes). Here's a sample datab...
Let's say you need to funnel random, related data given to you into more succinct categories. Example - You're given the following data. NOTE - There could be any number of other related, columnar data: Customer Product Category ========== ========= ================================= Customer A Product A ...
I'm looking for a good algorithm recommendation. I have Users and Achievements. Users create Achievements and then give them to other Users. Associated with each Achievement is the point value that the user specifies. A User's total points is the sum of all their achievements. Basically: Achievement : owner = Alias points = in...
Is there a good way to implement many-to-many relation between rows in single table? Example: table to store word synonyms: -- list of words CREATE TABLE word ( id integer PRIMARY KEY, word varchar(32) NOT NULL UNIQUE ); INSERT INTO words (id, word) VALUES (1, 'revolve'); INSERT INTO words (id, word) VALUES (2, 'rotat...
I have a mySQL database filled with one huge table of 80 columns and 10 million rows. The data may have inconsistencies. I would like to normalize the database in an automated and efficient way. I could do it using java/c++/..., but I would like to do as much as possible inside the database. I guess that any work outside the database w...
Hello, Imagine I have an Orders table with columns OrderID (PK), CustomerID, CustomerOrderN and so on. Now I need to add a possibility to "close" orders with specifying a reason why order is closed (for example "offered price is too high for customer", "not available", "customer asked to close the order"). Question 1. What would be ...
I am implementing a contest system in which the user has to choose the correct answer of multiple questions. Each week, there is a new set of questions. I am trying to find the correct way to store the user participations in a database. Right now I have the following data model: Participation Week +--------------+ ...
what is the best practice for database design for high traffic websites like this one stackoverflow? should one must use normalize database for record keeping or normalized technique or combination of both? is it sensible to design normalize database as main database for record keeping to reduce redundancy and at the same time maintain...
I write an application for drawing shops. I have these classes in my system: shop, cart place, rack and bakery. They have this properties: shop: X, Y, name, width, height, type, address cart place: X, Y, name, width, length, type, capacity rack: X, Y, name, width, length, type, height, balance_limit bakery: X, Y, name, width, leng...