How do you store a trie in a relational database?
I have a prefix trie. What is the recommended schema for representing this structure in a relational database? I need substring matching to remain efficient. ...
database-design
Hierarchical Data Structure Design (Nested Sets)
Hi, I'm working on a design for a hierarchical database structure which models a catalogue containing products (this is similar to this question). The database platform is SQL Server 2005 and the catalogue is quite large (750,000 products, 8,500 catalogue sections over 4 levels) but is relatively static (reloaded once a day) and so we a...
Identifiers in a one to many relationship
I have two tables, we'll call them Foo and Bar, with a one to many relationship where Foo is the parent of Bar. Foo's primary key is an integer automatically generated with a sequence. Since Bar is fully dependent on Foo how would I setup the primary key of Bar given the following constraints: Records for Bar are programatically gene...
Identifiers in a Diamond Relationship between Tables
I have four tables (A,B,C,D) where A is the parent of one to many relationships with B and C. C and D are parents to a one to many relationship with table D. Conceptually, the primary keys of these tables could be: A: Aid B: Aid, bnum (with foreign key to A) C: Aid, cnum (with foreign key to A) D: Aid, bnum, cnum (with foreign keys ...
Database Schema - Booking/Availability System
I was recently asked an interview question on a hypothetical web based booking system and how I would design the database schema to minimize duplication and maximize flexibility. The use case is that a admin would enter the availability of a property into the system. There could be multiple time period set. For example, 1st of April 200...
Database Table Structure for Shopping Cart
I'm a beginner with SQL and am working on one of my first databases. I am trying to create a very cut-down shopping cart and am currently working on getting the database together before I start coding the site. One of the things I am struggling with is how to structure the products portion of the database. If all my products were indivi...
Mysql ENUM type vs join tables
My requirement A table needs to maintain a status column. This column represents one of 5 states. initial design I figured I can just make it an integer column and represent the states using a numeric value. 0 = start 1 = running 2 = crashed 3 = paused 4 = stopped Since I don't want my app to maintain the mapping from the integer...
What happens when an auto-incrementing column runs out ?
Consider a simple table with an auto-increment column like this: CREATE TABLE foo ( `fooid` bigint unsigned NOT NULL auto_increment, ....snipped.... other columns PRIMARY KEY (`fooid`) ) ENGINE=InnoDB AUTO_INCREMENT=10 How does one redesign this so that we don't hit the max of the bigint datatype ? The unsigned range is 0 to ...
What's the optimal DB structure for a threaded forum?
I want to build a threaded forum for an elearning site (opensource asp.net mvc ofcourse, though this doesn't matter for this question). What should be the DB structure which will help retrieve the forum postings with optimum performance? I am not putting a no. to it as it may vary with the amount of rows being retrieved. Besides I s...
Is Guid the best identity datatype for Databases?
It is connected to BI and merging of data from different data sources and would make that process more smooth. And is there an optimal migration strategy from a database without Guids to a version with Guids without information losses? ...
MYSQL and RDBMS
I'm having difficulties to answer this question. Can someone help me? Discuss the benefits of MYSQL and explain why it is gaining acceptance as the RDBMS of choice for many organizations worldwide. ...
Mysql Visualization Tools
Hey everyone, Does anyone know a good (preferably open source and cross platform) tool to allow simple visualization of mysql databases? I just need a tool I can quickly point at a database and it'll show basic table structure and field types, etc. Nothing too advanced or crazy. ...
Dealing with application redesign
I am working on a project to redesign/ redo an application. The problem is that the old application was written in ASP.net 1.0 and the database is pretty large over 100 tables and 300 odd views.The database handles roles and membership in a very shoddy way. It involves a simple workflow too. The app is pain when it comes to maintainence....
Creating a Metatable table in a database
Does anyone have any suggestions for creating meta tables in a database? These tables would be used to emulate tables in a database, using a database. This is for when you want to easily add structure (more fields) to a database on the fly without having to worry about all the technicalities involved. The only example I have looks someth...
What is the best default transaction isolation level for an ERP, if any?
Short background: We are just starting to migrate/reimplement an ERP system to Java with Hibernate, targeting a concurrent user count of 50-100 users using the system. We use MS SQL Server as database server, which is good enough for this loads. Now, the old system doesn't use any transactions at all and relies for critical parts (e.g. ...
What is a good way to implement an agile database process, which is in synch with the code base, especially in regards to continuous integration?
The project I am working on were are trying to come up with a solution for having the database and code be agile and be able to be built and deployed together. Since the application is a combination of code plus the database schema, and database code tables, you can not truly have a full build of the application unless you have a databa...
DB Design: more tables vs less tables
Say I want to design a database for a community site with blogs, photos, forums etc., one way to do this is to single out the concept of a "post", as a blog entry, a blog comment, a photo, a photo comment, a forum post all can be thought as a post. So, I could potentially have one table named Post [PostID, PostType, Title, Body .... ], ...
DB Design: Does having 2 Tables (One is optimized for Read, one for Write) improve performance?
I am thinking about a DB Design Problem. For example, I am designing this stackoverflow website where I have a list of Questions. Each Question contains certain meta data that will probably not change. Each Question also contains certain data that will be consistently changing (Recently Viewed Date, Total Views...etc) Would it be bett...
Inserting taking longer on Sql Server 2005 table
I have a table with about 45 columns and as more data goes in, the longer it takes for the inserts to happen. I have increased the size of the data and log files, reduced the fill factor on all the indexes on that table, and still slower and slower insert times. Any ideas would be GREATLY appreciated. ...
What's the difference between making an index on 2 columns and an index on each of the columns separately?
I'm new to database indexing, if I have 2 columns in a table that are good choices for indexing like for example, [Posts]( [PostID] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](64) NOT NULL, [ApplicationType] [smallint] NOT NULL, ... ) in this case PostID would be the PRIMARY KEY CLUSTERED index, then I want to ...