Given the two following tables : Gallery id | title | desc Site id | title | desc | url I've a tags system which can apply to both Gallery and Site tables. I'm wondering if I should do : TagMap tagId | entityId | applyTo Where applyTo could be 'site' or 'gallery' or use separate table like the following : TagGalleryMap tagId | g...
I've been wondering whether constants like "Approved, disapproved, pending" or "Single, married, divorced" should be grouped up in a single table holding references to what module they are used at in the database or into separate tables for each relevant module. Which one is more practical? ...
Hi, I'm going to be developing a site where users will have the opportunity to edit previously submitted form data. What I have been asked to do is to track all edits via some form of versioning system in the database. I'm still not entirely sure of the structure of the data i'll be working with yet, but I'm trying to think of the best a...
Hi all- I am creating a site similar to the infamous Woot in one particular way: I would like to rotate through products at a rate of one per day. I am using RoR, and would prefer to dynamically choose a product, and not have to enter one each day. So here are the requirements: 1) Get a new product each day 2) Select dynamically f...
i am tracking exercises. i have a workout table with id exercise_id (foreign key into exercise table) now, some exercises like weight training would have the fields: weight, reps (i just lifted 10 times @ 100 lbs.) and other exercises like running would have the fields: time, distance (i just ran 5 miles and it took 1 hours) should...
We have a large number of data in many categories with many properties, e.g. category 1: Book properties: BookID, BookName, BookType, BookAuthor, BookPrice category 2: Fruit properties: FruitID, FruitName, FruitShape, FruitColor, FruitPrice We have many categories like book and fruit. Obviously we can create many tables for them (M...
When designing a stock management database system for sales and purchases what would be the best way to store the various taxes and other such amounts? A few of the fields that could be saved are: Unit price excluding tax Unit price including tax Tax per item Total excluding tax (rounded to 2 decimals) Total including tax (rounded to ...
I have three tables: Post, Attachment, and Media. Posts have Attachments, and Attachments have Media. Currently, the Post and Attachment tables are linked by foreign keys, and so are the Attachment and Media tables. My question is, for the sake of proper database design and normalization, should I setup a foreign key relationship bet...
I read about an inheritance feature in PostgreSQL that seemed pretty neat. Unfortunately I am forced to use MySQL. How would you do something similar in a clean way? Say you for example had the two following classes that you want to work with: User ˪ Id ˪ Name ˪ Password Employee : User ˪ Pay How would you store those in a MySQL...
Hi everyone, We are currently planning the database structure of a quite complex e-commerce web app that has flexibility as its main cornerstone. Our app features a large amount of data (products) and we have run into a slight headache trying to keep performance high without compromizing normalization rules in the database, or leaving ...
We have a few tables with persisted computed columns in SQL Server. Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations? The particular computed columns I am looking at conform some account numbers by removing leading zeros - an index is also created on this conformed account number...
Given that I have a table that holds vehicle information and one of those pieces of information is VehicleType (usually 6-20 characters), what are the technical reasons why it is better to design the tables like this: Vehicles VehicleID VehicleTypeID (INT) (relates to an INT in the VehicleTypes table) versus this: Vehicles Veh...
Hello, I am trying to design an mail system. I have stuttered on the problem that I have no solution yet, but maybe you can point me to some? My problem is I don't really imagine how does the database schema should look like if we are having two different tables for Inbox and Outbox. In example we also must have Users and Messages table...
This are few of the fundamental database questions which has always given me trouble. I have tried using google and wiki but I somehow I miss out on understanding the functionality rather than terminology. If possible would really appreciate if someone can share more insights on this questions using some visual representative examples....
Is it a good practice to initialize columns that we can know their values in database, for example identity columns of type unique identifier can have a default value (NEWID()), or columns that shows the record create date can have a default value (GETDATE()). Should I go through all my tables and do this whereever I am sure that I won'...
Hi, I need to organize cache in mySql database for address - coordinates. What is the best practice to store address? Do i need to compress address string or use it as is? edit: Ok, let's I reassert my question. How to store long (up to 512) string in database if I need to search by exactly this string in future. ...
Given that Java ME allows for no reflection and is quite stripped down, what would a good approach to storing data in a RecordStore be? I thought of devising a JSON-like syntax adapter class which would probably require every class whose values are to be stored to implement a Hashtable + might probably require an additional regex library...
Background: Professional tools developer. SQL/DB amateur. Setup: .Net 3.5 winforms app talking to MS SQL Server 2008. Scenario: I am populating a database with information extracted from a large quantity of files. This amounts to about 60M records, each of which has an arbitrarily sized message associated with it. My original plan w...
It is looking increasingly like I'll have to go live before I have had the time to tweak all the queries/tables etc, before I go live with a website (already 6 months behind schedule, so all though this is not the ideal scenario - thats how things are). Its now a case of having to bite the bullet. Its just a case of trying to work out h...
I've been hammering my head against my desk for the past few days on this, and so I turn to you, Stack Overflow. The software I'm working on has time-sensitive data. The usual solution for this is effective and expiration dates. EFF_DT XPIR_DT VALUE 2000-05-01 2000-10-31 100 2000-11-01 (null) 90 This would be easy. ...