database-design

Modular Application Database Structure

I am building a modular application. Through configuration you can turn these application modules on and off. I'm trying to determine what database structure (mssql2005) I should use for the tables that hold data for each of the modules. The two options that I've thought of are: Put all the tables into one big database and prefix th...

CMS versioning strategies for content.

I'm looking at building basic CMS functionality into our web product and am researching tips and design ideas for versioning content. I'm specifically looking for database schemas in use today. What are you using for content versioning? What patterns apply if you have different types of content that need versions stored? How is question...

Does it make sense to "fake" bitmap indexes?

I'm planning software that's an OLAP application at its heart (it helps analyse metering data) and is going to have some kind of star schema for its database, because the stored values will be looked at from different angles (time, source, type etc.) and the requests will be asking for aggregated data along these dimensions. The queries ...

Where do I start designing when using O/R mapping? Objects or database tables?

I'm starting a new database application and I wonder if it would be better to start the design at the objects (with UML) and build the database schema accordingly, or start at the design of the database (with ER) and create the objects accordingly. What are the pros and cons of either approach? (I dont think it matters but just in case...

Preventing Duplicate Inserts Into SQL With PHP

I'm going to running thousands of queries into SQL and I need to prevent the duplication of field 'domain'. Never had to do this before and any help would be appreciated. ...

What's the best way to store a title in a database to allow sorting without the leading "The", "A"

I run (and am presently completely overhauling) a website that deals with theater (njtheater.com if you're interested). When I query a list of plays from the database, I'd like "The Merchant of Venice" to sort under the "M"s. Of course, when I display the name of the play, I need the "The" in front. What the best way of designing the ...

What is the best practice to store a "saved search" in a database

I am working an a search page that allows users to search for houses for sale. Typical search criteria include price/zip code/# bedrooms/etc. I would like to allow the user to save this criteria in a database and email new homes daily. I could either: 1) Serialize a "SavedSearch" object into a string and save that to the database, the...

Is it possible to have a compound foreign key in rails?

Suppose the following data schema: Usage ====== client_id resource type amount Billing ====== client_id usage_resource usage_type rate In this example, suppose I have multiple resources, each of which can be used in many ways. For example, one resource is a widget. Widgets can be fooed and they can be bared. Gizmos can also be foo...

Is there an exchange format for ERD ?

Is there a way to exchange ERD diagram from one tool to other as XMI exists for exchanging UML documents? ...

Multiple databases vs a single database

I'm looking at an existing site and they are using separate databases. The databases seem to be setup in the following manner: general types (user_type, language, age, etc) member data (registration information & login) site configuration Personally, I would put everything into 1 database. To me this would make it easier than almost ...

How to design a database schema that can be used with both MySQL and SQL Server?

I'd like to give customers a choice of the database engine, but also want to minimize my troubles of such a decision. The engines in question are MySQL (5 or later) and SQL Server (2005 or later). ...

should nearly unique fields have indexes

I have a field in a database that is nearly unique: 98% of the time the values will be unique, but it may have a few duplicates. I won't be doing many searches on this field; say twice a month. The table currently has ~5000 records and will gain about 150 per month. Should this field have an index? I am using MySQL. ...

multivalued attributes in databases

How should a relational database be designed to handle multi-valued attributes ? edit: To elaborate: There are two ways I could think of for doing this - Trying something like putting comma separated values in the field, which appears a bit clumsy. Create another table for the field and let the multiple values go to the field. This ...

How to define structure in a tag-based organization?

[former title: Is there a way to force a relationship structure on a tag-based organizational methodology?] I have some entities, and they have a series of attributes. Some of the attributes affect what other attributes the entity can have, many of the attributes are organized into groups, and occasionally entities are requited to have ...

What's the best data type for a 1 character code column in a SQL table?

this is not a primary key, but merely used as a marker in existing system that marks these items with a 1 letter code ie 'N' for new, 'R' for return, etc. I was going to go with a varchar(50) as the data type but wondering if there's anything wrong with say varchar(2) instead aiming for efficiency. thx! ...

When is it time to create a 2nd database?

I have a mysql database which has grown to over 200 tables in it. I know this isn't a performance problem, but most admin tools I have aren't organized well enough to manage this number of tables easily, and I am thinking of putting some (new) tables into a 2nd database just for my own sanity. Has anyone else gone down this path, and wa...

Django: How do I model a tree of heterogeneous data types?

I need to store a tree data structure in my database, for which I plan on using django-treebeard or possibly django-mptt. My source of confusion is that each node could be one of three different possible types: root nodes will always be a type A entity, leaf nodes a type C entity, and anything in between will be a type B entity. I woul...

Is there any standard way of storing vector information in a database?

I want to store a very large amount of vector data on a server and only poll the parts I need at a given point... This shouldn't be a problem. Is there any way to take a vector file like an svg file and import it into a database? I could always write an svg parser to import it into my database, but is there any standard way of doing th...

Database Schema design

Hi, I am trying to design a location lookup in which the user can specify a location to any desired level of accuracy. eg. one of Country, State, City, Borough etc, I have a used a common location table, which will then be used in a lookup with the table name selected dynamically, but was wondering if there is a feasible alternative wa...

PHP MYSQL - Many data in one column

Hi, I need to store of 100-200 data in mysql, the data which would be separated by pipes.. any idea how to store it on mysql? should I use a single column or should I make many multiple columns? I don't know exactly how many data users will input. I made a form, it halted at the part where multiple data needs to be stored. Anyone kn...