I have a few tables representing geographical entities (CITIES, COUNTIES, ZIPCODES, STATES, COUNTRIES, etc). I need to way represent sets of geographical entities. A set can contain records from more than one table. For example, one set may contain 3 records from CITIES, 1 record from COUNTIES and 4 from COUNTRIES. Here are two possi...
What are the disadvantages to do so? ...
I am writing a data warehouse, using MySQL as the back-end. I need to partition a table based on two integer IDs and a name string. A more concrete example would be to assume that I am storing data about a school. I want to partition the school_data table based on COMPOSITE 'Key' based on the following: school id (integer) course_id (i...
Say, there is a Page that has many blocks associated with it. And each block needs custom rendering, saving and data. Simplest it is, from the code point of view, to define different classes (hence, models) for each of these models. Simplified as follows: class Page(models.Model): name = models.CharField(max_length=64) class Block...
I am writing a data warehouse, using MySQL as the back-end. I need to partition a table based on two integer IDs and a name string. I have read (parts of) the mySQL documentation regarding partitioning, and it seems the most appropriate partitioning scheme in this scenario would be either a HASH or KEY partitioning. I have elected for ...
If it matters as of now I'm using MySQL/MyISAM but I'm open to using PostgreSQL. I'm also open to using memcached. Consider a table that's used to store forum threads: id forum_name post_date 1 Hey! 2009-01-01 12:00:00 What's the best practice of storing thread-related entities such as votes, views, and counters? S...
I have a rails app that tracks membership cardholders, and needs to report on a cardholder's status. The status is defined - by business rule - as being either "in good standing," "in arrears," or "canceled," depending on whether the cardholder's most recent invoice has been paid. Invoices are sent 30 days in advance, so a customer who...
I have a MySQL table with 3 fields: Location Variable Value I frequently use the following query: SELECT * FROM Table WHERE Location = '$Location' AND Variable = '$Variable' ORDER BY Location, Variable I have over a million rows in my table and queries are somewhat slow. Would it increase query speed if I added a...
I'm planning to create a custom system for comments. I was wondering about comment moderation. For approving comments, is it as simple as just creating a field called "Moderated" in MySQL? What's a good suggestion for countering spam? Akismat? ...
I have four tables: users: id, thread_id threads: id, language_id posts: id, user_id, language_id languages: id USERS.thread_id is a foreign key to THREADS.id, POSTS.user_id is foreign key to USERS.id and POSTS.language_id foreign key to LANGUAGES.id. I can't delete an user because the POSTS.user_id will throw a foreign key constrai...
I have several SQL Server 2005 databases ranging from 20 – 600 tables in an application and no documentation. I am looking for a database diagramming tool that is smart enough to pick tables that seem to be related to one entity (e.g., tables related to Patient, tables related to Orders) or one functionality (e.g., Patient Management, Or...
I've shown up at a new job and discovered database which is in dire need of some help. There are many many things wrong with it, including No foreign keys...anywhere. They're faked by using ints and managing the relationship in code. Practically every field can be NULL, which isn't really true Naming conventions for tables and column...
Hi all developers, How can i store multiple values in one attribute. for example: column name: telephone i want to store 5,7 and 16 this three different values in one column (telephone) in one row. ...
Apologies for the less than ideal title; had a hard time coming up with something. Three entities: Products, Clients and Client_Product_Authorization. A Client has access to many Products. A Product is accessed by many Clients. A Client_Product_Authorization authorizes a Client to access a Product. We have 100,000+ Products (virtual...
If I had a site where a user can flag another user post and it cannot be undone or changed, do I need to have a primary key? All my selects would be on the post_id and with a where clause to see if the user already flagged it. ...
Is there an equivalent to cron for MySQL? I have a PHP script that queries a table based on the month and year, like: SELECT * FROM data_2010_1 What I have been doing until now is, every time the script executes it does a query for the table, and if it exists, does the work, if it doesn't it creates the table. I was wondering if I c...
My object model Person _______ public IList<Role> Roles { get; set; } public IList<Group> Groups { get; set; } Group _____ public IList<Role> Roles { get; set; } Role ____ public string Role { get; set; } So pretty much a standard roles based model, a User can exist in zero to many groups which have roles assigned to them, and can e...
I'm working on an app which I will be displaying events (sporting events, concerts, etc, etc). I'm trying to come up with a model where I can single out teams playing for sporting events, and bands/artists playing in a concert. My initial stab at is it to have an events table, team table, band/artist table. But I can't figure out the op...
Is there a reason for or against setting some fields as NULL or NOT NULL in a mysql table, apart from primary/foreign key fields? ...
Say I had some data, for which I want to fit a parametrized model over it. My goal is to find the best value for this model parameter. I'm doing model selection using a AIC/BIC/MDL type of criterion which rewards models with low error but also penalizes models with high complexity (we're seeking the simplest yet most convincing explanat...