Hi all, I am learning database design, given with the following business requirement. Which design approach should I take? Each customer can apply many applications. Each company can apply many applications. Each organisation can apply many application. Each approach, I thought of has a draw back. If you were to choose which will be...
I am building a website wherein the users can perform a variety of actions and they get a variable number of "points" or "badges" for performing specific actions. Certain data must be stored no matter which type of action the user performs, such as the user ID, the action type, a timestamp, the current point total, and any badge awarded....
I have a table with millions of IP range records (start_num, end_num respectively) which I need to query via a single IP address in order to return all ranges which overlap that point. The query is essentially: SELECT start_num , end_num , other_data_col FROM ip_ranges WHERE :query_ip BETWEEN start_num and end_num; Th...
I have the database table page with the fields contenttype_id and content_id. I want to link a page to a contenttype via contenttype_id and refer to an actual content via content_id. The problem is contents depend on the contenttype_id and are modelled differently, therefore the content_id refers to different tables depending on the cont...
More and more, I'm seeing searches that not only find a substring in a specific column, but they appear to search in all columns. An example is in Amazon, where you can search for "Arnold" and it finds both the movie Running Man starring Arnold Schwarzeneggar, and the Gund toy Arnold the Snoring Pig. I don't know what the term is for t...
I've already read these questions but the answers are either irrelevant or unsatisfactory. I have a number of checklists that are used as a guide for frequently repeated procedures. The checklists evolve slowly over time as each procedure is refined and improved. What I'm looking to do is store a number of distinct Checklists that each...
I could really use some good feedback on best practices / insight on whether it is better to put things in the database, or build an array. Take for example you have the suits of cards: $suits = array('clubs', 'spades', 'hearts', 'diamonds'); The array could change in the future, but not very frequently if at all. Now these suits are ...
I am building an inventory tracking system for internal use at my company. I am working on the database structure and want to get some feedback on which design is better*. I need a recursive(i might be using this term wrong...) system where a part could be made up of zero or more parts. I though of two ways to do this but am not sure wh...
Whats the most efficient way to search for a sub string in SQLite? I'm looking at the LIKE operator. Do I have the right idea? Has this worked well for you? http://www.sqlite.org/lang_expr.html Thank You. ...
Hi, Someone asked me today how they should store quest goals in a SQL database. In this context, think of an RPG. Goals could include some of the following: Discover [Location] Kill n [MOB Type] Acquire n of [Object] Achieve a [Skill] in [Skillset] All the other things you get in RPGs The best I could come up with is: Quest 1-* Que...
In our project we benefit a lot from the inheritance feature of the PostgreSQL database! We implement the dynamical creation of schemes for different agencies. This structure permits us to solve a lot of security issues which occurs immediately in the case of bulk tables (without partitioning). The only problem we encountered is to gu...
I'm trying to figure out the best way to map inheritance relationships in an object model into a relational database. For example consider the following class structure. public Class Item { public String Name{get; set;} public int Size {get; set} } public Class Widget:Item { public String Color{get; set;} } public Cla...
Suppose you have a table where you store the musical preferences of people, and you'd like to give users the ability to rank their preferences. At the time of insertion, the rank field should be calculated automatically. Later, however, the user should have the ability to edit his rankings. id, memberid, trackid, rank EDIT: It appears ...
Geo entities are: Continent, Country, Province, City, Neighborhood. By default Continent, Country and City will always exist. Province is optional as not all countries have state/province. And neighborhood data takes time to find, so it is a load as the data comes in. Other entity: Zipcode (mapping to City(required) and Neighborhood ...
I'm building the database for my Social Networking Site project. My database has to hold information of many user accounts. Each user has a profile and the profile contains various information. The account owner should be able to set the privacy of each profile information (either private or public). I'm wondering how I should keep track...
The best way to describe this scenario is to use an example. Consider Netflix: do they store their orders (DVD's they mail out) in a separate table from their member lists (NOT members table, but a joiner table of members and movies--a list of movies each member has created), or are orders distinguished by using additional information i...
I have a form where users submit different fields to create events. The number and type of fields requested are different on each form, depending on the category of event. What is the best way of going about designing this database - should the events contain every possible field and simply null the unused fields? Thanks! ...
I'm developing a real-time analytics solution and I need to choose the best database to do it. I believe MongoDB is ideal but I don't have the experience to compare all the other solutions. What's your advice? Thanks ...
Right now I have a table for matches where each match has a reference to 2 competing participants and a winner, but there isn't a way to store the order of the rounds. ...
Hi all, There are two methods which I noticed while designing child tables Method 1 Example: Master Table >>Order Table (order_id pk) Child Table >>order_products table (order_id,product_id, quantity,PK (order_id,product_id)) Method 2 example: Master Table >>Order Table (order_id pk) Child Table >>order_products table (order_pr...