I want to have two auto_increment column's per table, but mysql allows only one auto_increment columns. So, I tried replicating the oracle sequence using my own table. Here is the schema. create table logical_id_seq ( logical_id int auto_increment, primary key(logical_id) ); create table mytable ( physical_id int auto_incr...
I am planning to create a web application using spring/hibernate. How should I design my postgresql database in order to support inheritance? Assume that I have the following tables: super_table, sub_table_1, sub_table_2. Then sub_table_1 and subtable_2 would inherit super_table. Should I add a type column (like a char or int) so that ...
I've seen posts on SO and through google stating that with Mysql you cannot have multiple foreign keys of the same name. My issue is how do I reference the same column from one table in multiple other tables. In my case I have a FAMILY table that contains FAM_ID. I want this to be a foreign key in my DOCUMENTS and CONTACT tables because ...
I have these models(tables): USER, PHOTO, BOOK, QUESTION etc. And now I wanna add the favorite or like feature to the site, that is user can 'like' some photos or books or questions, and of course a photo can be liked by many users. So I wanna ask you how to implement this kind of thing. a page to show the books, photos, quesitons etc w...
If you had a fairly large amount of data (say a million rows) that you need returned in a specific order, one obvious way to do this is to simply put a numeric index on each row and order by that index. However, if you then want to create a new row in the middle of that data, you either need to hope that that there is a gap in the index ...
Here's a simplified example. UserDetails (UserID INT UserGUID UNIQUEIDENTIFIER Name VARCHAR(50) Age INT) UserRatings (UserID INT Rating INT Date DATETIME) UserVotes (UserID INT Votes INT Date DATETIME) The UserGUID only exists in the main table. In the application layer only GUID's are parsed around, never INTS. This is to prevent ...
Whats the best way to keep track of how many users and guests are online? Im making a forum for fun and learning Right Now I have a 2 fields in the users table called is_online and last_access_time. If current time is 5 minutes or more than last_access_time i set is_online it to zero. And if the signed in user refreshes browser i set i...
I'm currently working on a site that sells products of varying types that are custom manufactured. I've got your general, standard cart schema: Order has many LineItems, LineItems have one Product, but I've run into a bit of a sticking point: Lets say one of our products is a ball, and one of our products is a box of crayons. While peop...
Is there a benefit to having a single column primary key vs a composite primary key? I have a table that consists of two id columns which together make up the primary key. Are there any disadvantages to this? Is there a compelling reason for me to throw in a third column that would be unique on it's own? ...
I have to make a web application multi-tenant enabled using Shared database separate schema approach. Application is built using Java/J2EE and Oracle10g. I need to have one single appserver using a shared database with multiple schema, each schema per client. What is the best implementation approach to achieve this? - What needs to ...
I am designing an app that would involve users 'following' each other's activity, in the twitter sense, but I am not very experienced with database/query design/efficiency. Are there best practices for managing this, pitfalls to avoid, etc.? I gather this can create a very large load on the db if not done properly (or maybe even then?). ...
What's the best storage mechanism (from the view of the database to be used and system for storing all the records) for a system built to track whois record changes? The program will be run once a day and a track should be kept of what the previous value was and what the new value is. Suggestions on database and thoughts on how to store...
I have a table that holds agreement information. It works well for 95% of the agreements we record. But there is a certain type of agreement that would require another 6 or so fields to capture info specific to that type of agreement. My question is if its better to just add those 6 fields to the existing agreement table knowing that t...
I'm trying to figure out how to create a "sandbox" area in my new Windows .NET/SQL Server application. Here's the requirements: Users must be able to enter their own data for things like date ranges and pricing Users must be able to run multiple scenarios against this pricing and date range data The data structures above will also be u...
We have a table in our our database that stores XSL's and XSD's that are applied to XML documents created in our application. This table is versioned in the sense that each time a change is made, a new row is created. I'm trying to propose that we store the XSL's and XSD's as files in our Source control system instead of relying on the...
The only good reference that I can find on the internet is this whitepaper, which explains what database tiering is, but not how it works: The concept behind database tiering is the seamless co-existence of multiple (legacy and new) database technologies to best solve a business problem. But, how does it implemented? How does...
I've been assigned the task of creating a table that stores an email signature for each username. The question is, how should I store the signature block? I could use a regular varchar type, but then how do I store the formatting metadata? Any ideas or suggestions would be welcome. Thanks! ...
I'm trying to learn database design by creating a twitter clone.. And I was wondering what's the most efficient way of creating the friends' timeline function. I am implementing this in Google App Engine, which uses Big Table to store the data. IIRC, this means very fast read speed(gets), but considerably slower page queries, and this al...
I have the follwoing query which works but I'm wondering if it could be more efficient. I need the first and last name of the 4 employees from the phonebook table (pb) who's badges (the employees ID) are stored in the Commitment table SELECT Originator_ID, (SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Originator_ID) AS Origina...
I'm building the next Facebook!! No, kidding. I'm building a simple site to help me learn. It will allow users to log in with an email and password. There will be other information collected from the user that is pertinent to the sites functions. Using the supplied Kohana Auth module, should I store the separate data in another table...