I am developing a web application that can support threaded comments. I need the ability to rearrange the comments based on the number of votes received. (Identical to how threaded comments work in reddit) I would love to hear the inputs from the SO community on how to do it. How should I design the comments table? Here is the structur...
Hi, I need to store a sparse matrix on disk. It is like a database table with millions of rows and thousands of columns, where many or most columns are null. It needs to be queryable, like a SQL SELECT with a WHERE on some of the columns. My specific requirement is on Java. I first thought of using Berkeley DB for Java to simulate a tab...
I'm looking to create a table for user preferences and can't figure the best way to do it. The way that the ASP.NET does it by default seems extremely awkward, and would like to avoid that. Currently, I'm using one row per user, where I have a different column for each user preference (not normalized, I know). So, the other idea that ...
Duplicate of What’s wrong with foreign keys? I use MS Sql Server with a large database about 4 GB data. I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem. Should I use foreign keys? Will I get even more performance with fo...
I have a table, which have an auto-incremented PK and creation_date field, which is the unix timestamp. I am wondering why not lose the auto-incremented field and use the creation date field as the PK, as it is unique (I am using 1/1000 of a second accuracy). For: I am killing an indexed row. Against: there is a slight (very very slig...
Hi Sorry for the long winded title, but the requirement/problem is rather specific. With reference to the following sample (but very simplified) structure (in psuedo SQL), I hope to explain it a bit better. TABLE StructureName { Id GUID PK, Name varchar(50) NOT NULL } TABLE Structure { Id GUID PK, ParentId GUID (FK to Structu...
Hello All, I have a quick question - is there a best practice in SQL Table design for storing "either/or" data? I have the following problem - I need to store template data (defining folder structure) in a SQL table. Any given folder might have a static name (for example "Emails") or it might be dynamically generated for each instance...
Concerning design: Every user is part of a SOME group. (sales, parts, baseball players, whatever) Every "web page" gets seen by a group of people. (sales + parts + joe in accounting who got special permission) Every web page is usually part of a group of web pages on some level - you might try to keep these in some directory or start ...
I'm using Mnesia with Erlang, but this question applies to any key-value db like couchdb, etc. I'm trying to break free of the RDBMS thought process, but I can't wrap my head around how to efficiently implement this kind of schema. Say I have a User record, and he has many SubItemA records, which has many SubItem B records, so: User -...
We are currently in the process of solidifying a database change management process. We run MySql 5 running on RedHat 5. I have selected LiquiBase as the tool because it's open source and allows us to expand its functionality later if needed. It also seems to be one of the few free projects that are still active. Has anyone here had any ...
I had a revelation a couple of years ago when optimizing a database table that was running slow. The original query would take around 20 minutes in a table with large data-fields. After finally realizing that noone had bothered to index the table in the first place I built an index for a couple of the keys. Lo and behold, the query ran i...
Hello all, I have to collect statisctics by days, weeks, months and years of user activity for a site. I am the DB design stage and I wanted to do this stage properly since it will make my coding life easier. What I have to do is just simply increment the values in the fields by 1 in the DB each time an activity happens. So then I can ...
I will be storing a year in a MySQL table: Is it better to store this as a smallint or varchar? I figure that since it's not a full date, that the date format shouldn't be an answer but I'll include that as well. Smallint? varchar(4)? date? something else? Examples: 2008 1992 2053 ...
Lets say you have the following entities in your database: You want to be able to add a note to all three of the other entity tables (User, Client and Project). How would you set this up? Add a tableName and a FKId to the Note table? Create a separate table for each associated Note group (ie., ClientNote, UserNote) Create a separat...
When designing software with databases is it worth normalising enumerations into separate tables? e.g. if I have a class: public class Alert { public int ID public System.DayOfWeek AlertDay; public string Message; } Should my corresponding Alert table simply be something like: CREATE TABLE Alert ( ID INT IDENTITY, ...
Let's say I have a database column that should always be in uppercase. Here are a couple of ideas: 1) Create a column constraint of: col = UPPER(col) 2) Create an before insert/update row trigger that sets: col = UPPER(col) Usually the more constraints on your database data the better, and triggers can be mysterious and bad. Assume ...
Hi. I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database: How long should the VARCHAR field be in which I store the hash's result? ...
Hey, I have two tables: CREATE TABLE `Car` ( `car_id` int(11) NOT NULL AUTO_INCREMENT, `car_name` varchar(25), PRIMARY KEY(`car_id`) ) ENGINE=INNODB; CREATE TABLE `Tire` ( `tire_id` int(11) NOT NULL AUTO_INCREMENT, `tire_size` int(11), `car_id_FK` int(11) NOT NULL DEFAULT '0', PRIMARY KEY(`tire_id`), CONSTRAINT `R...
Does anyone have any recommendations for dealing with user-defined data structures? i.e. your users have to be able to define extra fields, and sometimes tables, to associate with the 'fixed' entities in your system. I always seem to be unlucky enough to end up working on projects where this is a major component. Normally the solution...
I'm working through a wide variety of procs that have a WHERE clauses that look like this: WHERE ... AND ( ( myTbl.myValue = 1234) or (myTbl.myValue = 1235) )-- Value = No I've talked this over with some colleagues and this sort of code seems unavoidable. I think it's a good idea to put this sort of code into one (and only one)...