Duplicate: Many to Many Relation Design - Intersection Table Design If I have these tables (* = primary key): user id* name group id* name Is this better? user_group user_id* group_id* Or is this better? user_group id* user_id group_id ...
While I can find the answer to almost any specific question I have concerning PHP + MySQL development, one resource I haven't been able to find is good solutions to advanced problems. I want to implement a minor messageboard type system that can be queried by AJAX, but it I have too many specifications for it to be filled by a prebuilt ...
I'm making a forum. I'm gonna store the id, subject, date, poster in a table named topics and keep the content in a table named posts. Now I just have to join them together nicely. And how should I do to connect the post with the topic? I haven't slept in 30 hours so I'm a little slow :P What do you guys think about this approach? ...
Suppose I have a customer object that has all of the standard customer properties (name, address, contact, etc). We get a new customer (Acme) who has a very specific request that invoices we send to them have our ID as a barcode. We don't want to this for all of other customers, so we intend on doing this as a special case for Acme. Now,...
Hi, I've got a table in my database where there can only be 1 row per non-identity attribute tuple. Whenever we insert a row, we first check to see if a row exists with those values, if not, we insert it, otherwise we use the ID from the existing row. In the example below, there would be a Unique Index on (Id) and (Attr1, Attr2, Attr3...
I have some tables that benefit from many-to-many tables. For example the team table. Team member can hold more than one 'position' in the team, all the positions are listed in the position db table. The previous positions held are also stored for this I have a separate table, so I have member table (containing team details) positions...
If you want to allow your site users to be able to login with Facebook Connect, Google Account, etc, how do you design your database so that they are all integrated? ...
I'm currently trying to work out the best way to store a business' hours of operation in a database. For example: Business A has the following hours of operation Monday: 9am - 5pm Tuesday: 9am - 5pm Wednesday: 9am - 5pm Thursday: 9am - 5pm Friday: 9am - 5pm Saturday: 9am - 12 Midday Sunday: Closed Currently I'm have a data model si...
I have a text file, it's a XML file. This is the configuration that the users upload. The issue now is should I store this file into the database straight? Or should I put the file in one directory, and inside my db I point to the location of this file? ...
Hi I'm looking to optimize my SQL. My database schema is: HOMES home_id address city state zip primary_photo_group_id HOME_PHOTOS photo_id (primary key) home_id (home primary key) photo_group_id (a photo group is the same image, resize from thumbnail to large size) home_photo_type_id (the size of the image be it a thumbnail or a...
As i described, I want program give me more helpful diagram for example : MS SQL Server Diagram didn't give you a good design for relations so if you have many relations in the same table then the diagram just draw many lines to your table and didn't set the lines to specific column that would confuse you if you have many tables and co...
It appears my SQL isn't limiting results based on price. In my previous post, http://stackoverflow.com/questions/1039771/sql-help-me-optimize-my-sql, people indicated that I should use a LEFT OUTER JOIN. SELECT homes.home_id, address, city, state, zip, price, photo_id, photo_url_dir FROM homes LEFT OU...
I am designing a new laboratory database. I want all the raw results (for all tests) in one table (RESULTS). However, the numerical values vary greatly in quantity and precision. For example, some results are simply a whole number count, while other results come from highly sensitive equipment. Which case below is ideal (and why)? O...
I am designing a new laboratory database. I want to store the raw results for all tests together. In some cases, the result is a single value. However, the result is sometimes a raw waveform or signal. Which case below is ideal (and why)? ... or provide your own ideal option. Option 1: Store each individual data point as a separate...
I'm currently in the planning phase of building a scheduling web app (for volunteer staffing of events), and I've got a question for those with more experience. Background: There's a calendar of events, and any user at any time can register for any of the events. At a later time, but before that event, one of the admins will step in an...
I'm storing an ordered list of several million items in a MySQL database. Reasonably often, items need to be added or removed from the list; equally often, the position within the list of an item must be determined. I'd say the read/write ratio is about 50:50. Starting with a linked-list model, I read [1] and the various models discusse...
We have to save the day and the month of a yearly reoccurring event. (Think of it as the day of they year when the invoice has to be sent to the client.) We will have to do some computation with this information. E.g if the customer is paying twice per year. Then we do not only have to check if this day is today but also if this day is ...
Hello dear fellow SOers, I have a system sitting on a "Master Server", that is periodically transferring quite a few chunks of information from a MySQL DB to another server in the web. Both servers have a MySQL Server and an Apache running. I would like an easy-to-use solution for this. Currently I'm looking into: XMLRPC RestFul Ser...
At work, we recently started a project using CouchDB (a document-oriented database). I've been having a hard time un-learning all of my relational db knowledge. I was wondering how some of you overcame this obstacle? How did you stop thinking relationally and start think documentally (I apologise for making up that word). Any suggest...
For example I have a tag lookup table joining tags to 3 different types of tables (ObjectTypes). Each has tags, but they are not shared. So I could do this Tagid | ObjectType | ObjectId | And when i join the table together I would just filter by object type before I joined. Now I know that this would break the ability to do a forei...