I have this three tables: CREATE TABLE userTypeA (   id VARCHAR (256) PRIMARY KEY,   nickname VARCHAR (256) NOT NULL,   password VARCHAR(70) NOT NULL DEFAULT '123456'   --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES );  CREATE TABLE userTypeB (   id VARCHAR (256) PRIMARY KEY ,   nickname VARCHAR (256) NOT NULL,   password VARCHAR(70) NOT NULL DEFAULT '123456'   --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES );  CREATE TABLE userTypeC (   id VARCHAR (256) PRIMARY KEY ,   nickname VARCHAR (256) NOT NULL,   password VARCHAR(70) NOT NULL DEFAULT '123456'   --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES );  Now, my idea was creating a table which has a foreign key that can reference the id of one of the three tables (userTypeA, userTypeB, userTypeC). In occasions it will reference table userTypeA, on other occasions it will reference table userTypeB and in others userTypeC. I tried this, but it seems to fail: CREATE TABLE account (     account_id VARCHAR (256),     user_id VARCHAR (256),     PRIMARY KEY (account_id),     CONSTRAINT accounts_users_fk1 FOREIGN KEY (user_id) REFERENCES userTypeA (id),     CONSTRAINT accounts_users_fk2 FOREIGN KEY (user_id) REFERENCES userTypeB (id),     CONSTRAINT accounts_users_fk3 FOREIGN KEY (user_id) REFERENCES userTypeC (id),   );  I've been looking for some examples but none looked like mine. Do you know any way to be able to reference one of the three tables with a single foreign key, or should I create different tables and structure them differently?
5970
Well here's my problem I have three tables; regions, countries, states.  Countries can be inside of regions, states can be inside of regions.  Regions are the top of the food chain.  Now I'm adding a popular_areas table with two columns; region_id and popular_place_id.  Is it possible to make popular_place_id be a foreign key to either countries OR states.  I'm probably going to have to add a popular_place_type column to determine whether the id is describing a country or state either way.
28397
I have these two CREATE TABLE statements:   CREATE TABLE GUEST (   id int(15) not null auto_increment PRIMARY KEY,   GuestName char(25) not null );  CREATE TABLE PAYMENT (   id int(15) not null auto_increment   Foreign Key(id) references GUEST(id),   BillNr int(15) not null );   What is the problem in the second statement? It did not create a new table.
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.
112010
I am using  on a Drupal 6 to Drupal 7 migration right now.  I have three types: type1, type2, type3.  Each of the three types has a entityreference field on it, which is configured to be able to point to all three types.  This means that there can be circular references, and when a particular node is being migrated, the destination node may not exist yet and could potentially be any of the three types.  My migration class looks like this:  class MyNodeMigration extends DrupalNode6Migration {   public function __construct(array $arguments) {     parent::__construct($arguments);      $this-&gt;addFieldMapping('field_legacy_nid', 'nid');      $this-&gt;addFieldMapping('field_the_reference', 'field_the_reference')       -&gt;sourceMigration(array('type1', 'type2', 'type3');   } }  $node_arguments = array(   array(     'class_name' =&gt; 'MyNodeMigration',     'description' =&gt; 'type1',     'machine_name' =&gt; 'type1',     'source_type' =&gt; 'type1',     'destination_type' =&gt; 'type1',     'dependencies' =&gt; array('roles', 'users'),   ),    array(     'class_name' =&gt; 'MyNodeMigration',     'description' =&gt; 'type2',     'machine_name' =&gt; 'type2',     'source_type' =&gt; 'type2',     'destination_type' =&gt; 'type2',     'dependencies' =&gt; array('roles', 'users'),   ),    array(     'class_name' =&gt; 'MyNodeMigration',     'description' =&gt; 'type3',     'machine_name' =&gt; 'type3',     'source_type' =&gt; 'type3',     'destination_type' =&gt; 'type3',     'dependencies' =&gt; array('roles', 'users'),   ), );  $common_node_arguments = $common_arguments + array(   'user_migration' =&gt; 'users', );  foreach ($node_arguments as $arguments) {   $arguments = array_merge_recursive($arguments, $common_node_arguments);    Migration::registerMigration($arguments['class_name'], $arguments['machine_name'], $arguments); }   When I run the migrations, most nodes are fine, but I get a decent amount of Stubs (*) created.  Some of the destination nodes have the entityreference pointing to these Stub nodes.  The source nodes are fine.  What I have uncovered is that the bundle on the destination nodes that are being made as stubs are incorrect, and look like they are all type1.  A node of the proper type does end up being created, but it never gets set as the entityreference target.  Another way to explain this is that I end up with   node: nid=123, type=type1 node: nid=234, type=type1, field_the_reference=123 node: nid=345, type=type2   where it should be   node: nid=234, type=type1, field_the_reference=345 node: nid=345, type=type2   How do I handle this situation?  (*): a "Stub" in the context of this question here refers to a node which is created for the purpose of being a reference target, but does not get its data initially. The term is used throughout the Migrate documentation and source code.
67071
When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?  The reason that I am asking this question is that when we use UNIQUE INDEX (col1, col2, col3), it acts as INDEX for the first column only, and we need to create additional INDEXs for other columns. I want to know if that's the case for Composite Primary Key too.
61586
I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it.    value 4 is dependent on the other value 1,2&amp;3. even if one of the value changes the combination changes and the fourth value is affected. Each of the value 1,value 2,value 3 are independent on each other. Many-to-many relation between value 1,value 2 and value 2,value 3. value 4 is not derived or computed but a distinct value for all the combinations. So how can I design a database for it?
4453
I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:   Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.   Look at the following fiddle:   I am working with MySQL.  I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:  UPDATE father  SET `col1` = "UPDATE_MY_ID_PLEASE"  WHERE `col1` = 'A' AND `col2` = 1;   Is the design wrong? How should this be done?
47138
I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?   My table definition:  CREATE TABLE mytable (     id serial primary key,     col1 int NOT NULL,     col2 int NOT NULL,     unique (col1, col2) )
41856
I have a business requirement to have a FK field on TableA referencing TableB.  If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.  Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?
64355
Our company would like to understand the effects:   If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),  and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.   Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?  Or was SQL designed to be successful even in multithreading parallel insert?  Note: IDENTITY_INSERT is OFF
114689
I am practising in writing a simple forum and just can't decide on table structure. Currently I have this:      id  int(11)     AUTO_INCREMENT           title   varchar(56)      group_id    int(11)     group_parent    tinyint(1)     access_from int(11)      alliance_forum  tinyint(1)                alliance_id int(11)     alliance_role_access    int(11)   Group parent is a 0 or 1 column which states wherever a forum is parent of forums, and group id is for group forums whch reffers to same table to record with group parent set to 1. Alliance forum is same as group parent, as the alliance id,and access column are for access control. Which would be the best and perfomant way of restructuring this? Should I mak 3 different tables for alliance forums, forum_groups and group_forums or keep it this way? P.s. Please remove my  another same question.
99212
I have a table with many attributes that can be grouped in logical groups, and thus the idea of putting these attributes in separate tables seems attractive.  The logical groups don't represent entities themselves.  When one attribute in the group is set, most of the rest of the attributes in that group also must be set (but not all).  The fields's type of the groups generally are VARCHAR(15-20).  There's no BLOB or TEXT fields either.  The logical groups don't represent subtypes, because they aren't mutually exclusive.  The entity is requested more for reading than for writing.  Hence a large table looks appropriate.  Also, this way the joins are avoided in queries.  The part of this approach I don't like is the large number of nullable fields.   Looking for Expert advice.
99266
I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. The output will be  Prod_Id -->>>  PROD0001, PROD0002 and so on. Now if the product name is RACK it should create a id RACK_001 with the start two letters of the product name and auto increment automatically.  For all Product name it should create accordingly,how to do this.  As of now i am using a trigger for auto increment  in varchar data type. Help with the trigger code.  DELIMITER $$   CREATE TRIGGER tg_product_details_INSERT   BEFORE INSERT ON product_details   FOR EACH ROW    BEGIN      INSERT INTO product_details_seq VALUES (NULL);      SET NEW.Created_Date = NOW();      SET NEW.Submitted_Date = NOW();      SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;      SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));    END DELIMITER ;   product name -->> BOOK, SHOE, DRESS, FURNITURE  for all book it should create a autoincrement id BOOK_001, BOOK_002  and for all Shoe it should create a autoincrement id like SHOE_001, SHOE_002  and so on...!!!  Thanks, Acube.
59131
I'm creating a user-based website. For each user, I'll need a few MySQL tables to store different types of information (that is, userInfo, quotesSubmitted, and ratesSubmitted).  Is it a better idea to:  a) Create one database for the site (that is, "mySite") and then hundreds or thousands of tables inside this (that is, "userInfo_bob", "quotessubmitted_bob", "userInfo_shelly", and"quotesSubmitted_shelly")  or  b) Create hundreds or thousands of databases (that is, "Bob", "Shelly", etc.) and only a couple tables per database (that is, Inside of "Bob": userInfo, quotesSubmitted, ratesSubmitted, etc.)  Should I use one database, and many tables in that database, or many databases and few tables per database?    Edit:  The problem is that I need to keep track of who has rated what.  That means if a user has rated 300 quotes, I need to be able to know exactly which quotes the user has rated.  Maybe I should do this?  One table for quotes. One table to list users. One table to document ALL ratings that have been made (that is, Three columns: User, Quote, rating).  That seems reasonable. Is there any problem with that?
122704
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?   What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.   This is my current setup:  CREATE TABLE `invoices` (   `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,   `invoicenumber` mediumint unsigned NOT NULL,   `branch` enum('A','B') NOT NULL,   `date` date NOT NULL,   `client` varchar(100) NOT NULL ) COMMENT='' ENGINE='InnoDB';   To check the lateset invoice, I run:  SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'
25040
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).  CREATE TABLE  `clients` (   `clientId` int(10) unsigned NOT NULL auto_increment,   `clientName` varchar(100) NOT NULL default '',   `courseNames` varchar(255) NOT NULL default '' )   The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"  CREATE TABLE  `clientenrols` (   `clientEnrolId` int(10) unsigned NOT NULL auto_increment,   `studentId` int(10) unsigned NOT NULL default '0',   `courseId` tinyint(3) unsigned NOT NULL default '0' )   The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.  Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")  Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:  SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`] FROM ...
101869
I have one recordtype named 'India' in both Account and Contact Objects.My requirement is whenever i create a new account record of type 'India' a new contact record of type 'India' should be created automatically.  i tried with the following code:  trigger AccRecTypeTrigger on Account (before insert) {     List&lt;RecordType&gt; recType = [select id,name from RecordType where sobjectType = 'Account' and isActive = true];    Map&lt;String,Id&gt; m = new Map&lt;String,Id&gt;();    for(RecordType r : recType){      m.put(r.name,r.id);    }     List&lt;Contact&gt; conlst = new List&lt;Contact&gt;();    for(Account a : trigger.new){        a.recordTypeId = m.get('India');        Contact con = new Contact();        con.accountId = a.id;        con.recordTypeId = a.recordTypeId;        con.recordType.name = a.recordType.name;         conlst.add(con);       }    insert conlst; }   But i am getting following error when i try to save     Apex trigger AccRecTypeTrigger caused an unexpected exception, contact   your administrator: AccRecTypeTrigger: execution of BeforeInsert   caused by: System.NullPointerException: Attempt to de-reference a null   object: Trigger.AccRecTypeTrigger: line 14, column 1   Please help me how to achieve this......