I was just wondering how woudl the database structure of NING would be. would it be creating seperate tables for each of the site created in it? or would it be using same table for different modules with a website id column in each table? if i would need to search about this kind of database stucture? and reference to read about? ...
For make a document managment system, I'm looking at document stores like MongoDB, but because I have more experience with regular databases (Firebird, Sql Server, Mysql) I wonder if is possible model a document store on top a relational one. The advantages about a document store, schema less: Fit well to the task of store arbitrary m...
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...
I always struggle with dealing with normalised data, and how I display it. Maybe its because I don't fully understand the normalisation rules, like how to get it fully into Boyce-Codd. Performance is not really an issue at this stage, though maintainability of the schema is. user ID Name 1 Alice 2 Bob 3 Charlie skills ID ...
What are the disadvantages to do so? ...
i have a table called orders. one column on order is customer_id i have a table called customers with 10 fields Given the two options if i want to build up an array of order objects and embedded in an order object is a customer object i have two choices. Option 1: a. first query orders table. b. loop through records and query the pe...
i asked this question around a single join or multiple (select n + 1) queries i wanted to find out if this was the same if you had many to many relationship and a lot of bridge tables for example, here are my tables: Table: People (id, first, last, age, phone, etc . .) Table: Roles (id, name) Table: PeopleRoles (id, personID, roleID)...
i have the following tables: Table: People (id, first, last, age, phone, etc . .) Table: Roles (id, name) Table: Skills (id, name) Table: People_Roles (id, personID^, roleID^) Table: People_Skills (id, personID^, skillID^) ^ = foreign key I basically want a query that gives me the full result set of all people and their roles and the...
Two examples are: Columns that will show up in a queries where clause (where Name = "xtz") Columns that you are going to order (sort) on in queries Is this correct and are there other important use cases? Can SQL Server recommend fields to index based on usage patterns ? ...
I am trying to implement a system where emails to large (or small) groups of people are scheduled to be delivered by a cron job (instead of in a loop, while the user is waiting for them to finish sending). There are two types of email a user could send: an email to everyone in the subscribers table, or an email only to members of a grou...
When writing my own flat file databases I try and keep the file sizes as small as possible, when designing mySQL databases I put all my tables into one database (I'm under the belief that mySQL stores each table in it's own file). I'm new to sqlite and my ethics clash - a whole database stored in one file. I know the recommended size is...
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...
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 ...
I'm creating a sports statistics database. With it, I'd like to catalog game/match statistics for many types of sports. For example, this database would be able to tell you how many touchdowns the Carolina Panthers scored in the 09-10 season (football), or how many free throws were made by the Miami Heat in their last game (basketball). ...
I m really looking for some input and guidance here. Suppose you need to model a financial trading system. This needs to be able to handle different products (eg Equities(Shares) / Commodities (Oil /Gold) and Fixed Income (Bonds) Now each product has different characteristics The way I think about this is to have one "Transactions" ...
I have a MySQL database with the following table structure: TransactionType: Transaction_Amount, Transaction_ID, Transaction_Form Transaction: Transaction_ID, Timestamp Purchase: Transaction_ID, Item_ID Items: Item_ID, Client_ID This is really a two part question (please let me know if i should post these as two separate questions) 1)...
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 database of automobile classified listings. After 90 days, the classified listing is no longer valid to be displayed (the listing expires); however, I want to retain the listing for archive purposes. Question: From a database design best practice perspective as well as query performance, is it better to keep the old listing A)...
Hello, the problem is as follows: We're using hibernate with annotations as O/R Mapper. Some @Column annotations look like: @Column(columnDefinition = "longblob", name = "binaryData", nullable = true) or @Column(columnDefinition = "mediumtext", name = "remark", nullable = true) with the columnDefinition attributes being mysql speci...
When I run the EXPLAIN command on my MySQL query: EXPLAIN SELECT colZ FROM table1, table 2 WHERE table1.colA = table2.colA AND table1.colB = table2.colB AND table1.colC = X The EXPLAIN command states that the possible_keys include: colA, colB, colC But the actual key used as colA Question: Does this imply I should make an index o...