I have a table which needs to link one of three seperate tables, but it should only link to one of them, e.g. Main_Table id UNIQUEIDENTIFIER t1_id UNIQUEIDENTIFIER t2_id INT t3_id INT T1 id UNIQUEIDENTIFIER name VARCHAR(255) T2 id INT name VARCHAR(255) T3 id INT name VARCHAR(255) Is it possible to have a constraint whereby only one...
hey.. i need to place a "comments, faq, contact us" table on my site where users will place their name and the comment they want to say to the admin of the site.. some reports about a game they've played.. the "comments" then it will be saved inside an mysql database ("games") within the given "name" and the "ipaddress" of the user (comi...
Almost every time that I build an application, I find myself creating an 'address' table to contain addresses for the system. What columns do you create within your 'address' table (or tables if you normalize addresses over more than one) and what is your reasoning behind it? ...
i am currently working on a project where i have 'units' that will have three different 'phases' and three 'steps' to each 'phase'. I am having trouble designing the layout of my database whereby i dont not know whether to put it all in one table or multiple tables. for instance: table 1: unit_id, unit_category, unit_name, unit_phase, ...
In one of my very previous company we used to have a separate table that we stored long descriptions on a text type column. I think this was done because of the limitations that come with text type. Im now designing the tables for the existing application that I am working on and this question comes to my mind. I am resonating towards s...
This is a new question which arose out of this question Due to answers, the nature of the question changed, so I think posting a new one is ok(?). You can see my original DB design below. I have 3 tables, and now I need a query to get all the records for a specific user for running_balances calculations. Transactions are between user...
Hello, I'm currently working on improving my database to make room for growth. As it stands, different users have different 'permissions' to areas of the website. Some users have permissions to multiple areas of the website. I'd like some feedback if I'm doing this in the most efficient way: tblUsers: usrID usrFirst ...
I was going over AdventureWorks2008 database and wanted to create a new table that associates a product to a sales person. There is a many-to-many relationship between those tables. The question is, Of two schemas, Sales and Production, does ProductSalesPerson table belong to? ProductSalesPerson doesn't neccessarily belong to either ...
hey everyone... so i'm working on a database design class for university. I've got the question below and my attempt at the diagram here http://tinypic.com/view.php?pic=httchc&s=3.. would anyone mind taking a look and offering suggestions? thanks for the help!! QUESTION: Question 3 The following situation describes a company that ...
For example, "Dole Banana" is a kind of product, it's listed under the "Bananas" category, when I open the "Fruits" category, I want to see "Dole Banana". + Food |--+ Fruits |------+ Bananas |------+ Apples |--+ Vegetables |------+ Onion |------+ Spinach ...
I have records (Contacts, Addresses etc.) which should be accessible to any of the following (incl. combinations, e.g. 2 groups and 4 individuals): Everybody Members of multiple Groups/Departments Members of a single Group/Department Multiple Individuals Single Individual What is a good database structure to realize this? Basically i...
Consider that there is a bunch of tables which link to "countries" or "currencies" tables. For making data easier to read I'd like make CHAR field with country code (eg US, GB, AU) and currency code (USD, AUD) a primary keys in each of those 2 tables and all other tables will use this CHAR as a foregin key. Database is mysql with innod...
I would like to design a database that is accessed through a very slow network link. And luckily the database itself is pretty static. So I'm going to use aggressive caching of the results. From time to time, other insertion and updates may happen on tables while the client is running, so I would like to design a low-bandwidth system whe...
I am building a table for tracking the history of particular objects in my database. Currently I have following columns: HistoryId int IDENTITY(1,1) NOT NULL HistoryDate datetimeoffset(7) NOT NULL HistoryTypeId int NOT NULL HistoryDetails nvarchar(max) NULL For the most part each history item will be self-explanatory through the His...
Have you ever noticed how facebook says “3 friends and 33 others liked this”? I was wondering what the best approach to do this is. I don’t think going through the friends list, and the list of users who “liked this” and comparing them is efficient at all! Do they keep a track of this in the database? That will make the database size ver...
I'm looking for a good tool to help me draw ER (entity-relationship) Diagrams on Windows ...
My site is going to have many products available, but they'll be categorised into completely different sites (domains). My question is, am I better off lumping all products into one database and using an ID to distinguish between the sites, or should I set up a table and /or DB per site? Here are my thoughts SEPARATE DATABASES Easie...
Time and again, I've seen people here and everywhere else advocating avoidance of nonportable extensions to the SQL language, this being the latest example. I recall only one article stating what I'm about to say, and I don't have that link anymore. Have you actually benefited from writing portable SQL and dismissing your dialect's prop...
I'd like to have a single source of the description of the data structure. Some people are asking can the DBML file being refreshed when it is changed in the database. The way I do is stupid but common; open it, delete all, and drag-drop again. I heard that there are some 3rd party do the tricks. But I am thinking, any way to inverse t...
I and my friend argued with each other about the design of a database. He argues that to ensure the integrity of a complex database is better to use triggers. I believe that for this purpose is better to use keys (primary, unique), and constraints. I think that the use of triggers is dangerous, because they work "behind the scene" and...