Is there a way in sqlite to automatically save a timestamp in the table whenever a record has been created that I can then grab and save as an NSDate in my application? Or should I instead create the NSdate object with the current time in my iphone app, and then insert it into the database. If so? What column should the datatype be for...
I want to design a database for my application, and I have a question about the way to define a relation between the entities. I have 2 related entities which ID is compound. For example: an address entity which are identified by the properties [street number, house number, city, country], and a phone number (xx-yyyyyy) an entity whi...
On any website, such as on StackOverflow, each question has a view count, and user reading a question but has previous read it won't count twice. I have some ideas about how it is implemented and using what tables to do it. What do you think is the best way to implement this? ...
I have a 2 tables: a membership table and mailing list table. [Members] Username Password EmailAddress MailingListOptIn [bit] Planes [bit] Boats [bit] Cars [bit] The fruits columns are user preferences. and [MailingList] EmailAddress MailingListOptIn A mailinglist'er can opt to receive newsletters but may not necessarily be a ...
I have been googling a lot and I couldn't find if this even exists or I'm asking for some magic =P Ok, so here's the deal. I need to have a way to create a "master-structured" database which will only contain the schemas, structures, tables, store procedures, udfs, etc, everything but real data in SQL SERVER 2005 (if this is available ...
I have been deliberating over the best way to hold a 'Live' set of data and a draft set of data in the database. The live version is displayed on a website and the draft version is worked on until until it is ready to go live. The model is relational and consists of many tables. My current method is to have 2 databases, one for draft an...
I need to create the typical crud app for "articles", is there a standard or sort of a best practice when it comes to this? I know every app and situation varies, but I mean generally. Secondly, I'm confused a bit on how to handle multiple images associated with a particular article, I was thinking just img tags strewn through out the b...
I am trying to program a plugin to bbPress (the open source forum software) that will work similar to Hacker News (http://news.ycombinator.com/). Specifically, I want to sort the order of forum-threads (bbPress calls them "topics") using the following algorithm: sort_value = (p - 1) / (t + 2)^1.5 where p = total votes for each topic fr...
I am designing a simple auto parts database for my friend to learn more about database design. I was stuck with the ORDER, ORDERITEM, SHIPMENT, PACKING LIST, INVOICE and CONTAINER relationship. The ORDER contains ORDERITEM which is individual parts (can be more than one in amount on one entry). When all or part of ORDERITEM are availa...
To adhere to 1st normal form, one of the things you must avoid is repeating groups. As in instead of: CustID Name Address Phone1 Phone2 Phone3 102 Jerry 234 East.. 555-2342 555-9854 555-2986 You should create a second Telephone Number table and then on a join you would get: CustID Name Ad...
Hi guys, im in the beginning of the complete restyle of an my web application, and i have some doubt about a good database-design that can be reliable, query-performance, and in the same time fully customizable by the users (users wont customize the database structure, but the funcionality of the application). So, my actual situation is...
It it ok to have a table with just one column? I know it isn't technically illegal, but is it considered poor design? EDIT: Here are a few examples: You have a table with the 50 valid US state codes, but you have no need to store the verbose state names. An email blacklist. Someone mentioned adding a key field. The way I see it, t...
Consider a database with tables Products and Employees. There is a new requirement to model current product managers, being the sole employee responsible for a product, noting that some products are simple or mature enough to require no product manager. That is, each product can have zero or one product manager. Approach 1: alter table ...
A couple of summers ago I worked for company that needed an Access DB designed for their sales and record keeping. One thing that bugged me during that summer was sometimes not being able to change the datatype of a column, delete a column, or delete a row (entry). And, if memory serves me (this was five years ago), I think I sometimes c...
I know some patterns to DB table schema: one-one: same table. one-many: Having 'many' table's primary key a member of 'one' table. many-many: Having a new relationship table contains both table's primary key. What are some other patterns? ...
A coworker sketched out the values of a new table as: "Foo", "some value 1" "Foo", "some value 2" "Foo", "some value 3" "Bar", "some value 3" These are the only columns in the table. The column names are Col1, Col2. One person said that this table is not normalized, another said it is. The specific argument that it violated norma...
I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work. Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category i...
Hi all, I am a fairly new MySQL developer and am starting on a project that I could do with a bit of initial advice on... I am creating a database that will primarily be holding a certain number of items (between 1-5k) and around 40 boolean variables associated with each one. Users will then be inputting their choice of these 40 value...
I have given an assignment to get the feasibility for making a dll which takes some inputs such as database name(Mysql,access, sql, oracle etc.) and some more inputs to generate a query and based on that the dll should return a recordset to the application. is it a good idea. if yes then what inputs should i consider ...
I am a big fan of the design and functionality of Stack Overflow. So, I would like to create my own design for a commenting system based on the one used for this site! Questions: What sort of table structure should I use for storing comments? What SQL queries might I then use for fetching stored comments? ...