What are the tradeoffs in choosing a TEXT datatype as compared to a BLOB or BINARY datatype? I don't intend to index on the column or use it in a WHERE clause, it's just data in the database, that happens to be textual. If there's a performance or storage advantage to my choice of datatype though, that would be good to know... Thanks! ...
Our real estate application has a table, Events, which has historically been linked to the Homes table via an Event.homes_id column. Recently, for the first time, we added an event type which is not connected to a home, but a realtor. The question: is it good practice to now add a realtor_id column to the Events table? Something in me...
How can generic facts be stored in a database such as '3 injured in Kansas fire' or 'Spain wins 2010 Worldcup'? The end result would be to have some table(s) where if multiple entries for people being injured or multiple entries for sports scores - data could be queried/totaled. desired query would be to see how many injuries have oc...
Hi, I am trying to design a HA solution for an active-active site. Basically, Site A and Site B are both writing to the same table in a database that is position outside of Site A and B. This database is use in an event when either Site A or B fails, it could be use as a backed up. MySQL is being used here. So i am wondering is there a...
Here is the situation: [Job] belongs to one [Job Category], which [Job Category] is belong to one [Industry] So, my initial design is like that: A Job have a fk to find the Job Category, and a Job Category have a fk to reference to Industry. But the problem is when I want to know the job is belong to which industry, I need to find the ...
I have a hits table with millions of records. I need to show some charts on the data of the last month. Does the solution of create a view something like : CREATE VIEW ReportMonth AS SELECT * FROM Report WHERE DayDate > DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 30 DAY) is good in terms of performance , Is there a better s...
Hi, I'm building a prototype for a project where you are supposed to exchange stuff. How would you suggest I layout the database for the easiest query to match users with items they want to trade (I want to get rid of this/I want to get hold of that)? Thanks in advance! ...
I have an online shop where users can have little shops with their own products. Each of this products can have questions associated to it and the owner of the shop has the ability to answer those questions. This information is stored in 3 tables a "Questions"(QuestionID,ProductID,...) table, a "Products"(ProductID,ShopID,...) table and ...
I have a PHP MySQL application which has a modular design. There are about 5 modules total now, and I don't really foresee there ever being more than 10. I currently have 2 types of users - "User and Admin". These user types have the same perms except the Admins can edit users, whereas regular users can't. I want to implement modular p...
Hi, I have joined a project which has been already developed and its a legacy project with very large database and its in Java/J2EE. I am trying to understand the Database Design & Architecture and so is there a tool available which can generate schema from the populated table representing all the constraints like foreign keys etc ? ...
I realize this question is general, but I think people with a lot of experience in event tracking will have good insight. I have a website where I want to track file downloads by user. Two methods come to mind: 1) Create a model called AssetDownload and populate that with the data. 2) Create a model called Event or Activity and have th...
So I am trying to design a database that will allow me to connect one product with multiple categories. This part I have figured. But what I am not able to resolve is the issue of holding different type of product details. For example, the product could be a book (in which case i would need metadata that refers to that book like isbn, ...
Looking for any suggestions or advise or even best practices. I have developed an online database using php and mysql. It allows companies to log complaints and resolutions etc. There is a user database for login and a cip database for logging the main data. 2 companies are trialing and testing the database. At the moment each compan...
We have several web applications that create a shopping cart, save it to a database, then redirect to a centralized web application to process and accept payment for the shopping cart. Right now, we are using GUIDs for the shopping cart IDs and passing those GUIDs in the querystring to the payment application. We are using GUIDs so tha...
Hi all, I have a table products and table sizes. Each product can have multiple sizes. How can I design my database in order to not have repeated entries of the same product? Thanks in advance ...
I need to store the number of plays for every second of a podcast / audio file. This will result in a simple timeline graph (like the "hits" graph in Google Analytics) with seconds on the x-axis and plays on the y-axis. However, these podcasts could potentially go on for up to 3 hours, and 100,000 plays for each second is not unrealisti...
Using openID has brought up some questions that I'm hoping the SO community can help me answer. OpenID and the (now obsolete) Username From what I've read, one of the main goals of openID is to eliminate the need for someone to create yet another username and password for some site they may only use once. While I think this is a reall...
Hi, I would like create a table and add to it a Primary Key. As for my understanding MS SQL add a clustered Index on the Primary Key and will name it with a default name. I would like to know if is possible create a table and ASSIGN a custom name for the index created by default or how can i change the default name after the table as ...
I'm planning a database who has a couple of tables who contain plenty of address information, city, zip code, email address, phone #, fax #, and so on (about 11 columns worth of it), a table is an organizations table containing (up to) 2 addresses (legal contacts and contacts they should actually be used), plus every user has the same in...
Hi all, I have a database with these tables: products,colors,sizes Each product(t-shirts,jeans etc) can have multiple colors and each color can have multiple sizes EDIT Products can share colors and colors can share sizes So I've made an xref_products_colors table and xref_color_sizes table. Is that OK or there is a better way to d...