New answers tagged schema
I started working with databases in the 70's and tend to use data dictionary to mean the physical representation of data, and the schema to mean the logical model. A schema is like a blueprint that helps you visualize the way the data is organized. A data dictionary is the list of table names, column names, lenghts, variable types and other specific, ...
MySQL should not have trouble handling a large table like that. You have it indexed (because of the foreign keys) so most queries against the table should use one of the indexes and be fairly fast even if there are a huge number of rows in the table.
This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases: create table test ( test_id integer not null, constraint pk_test primary key (test_id) ) ; create table test_case ( test_id integer not null, case_no smallint not null, ...
here's a quick and ugly query that make the job : with nsp_oid as ( select oid from pg_namespace where nspname='mySchema' ), dep_oid as ( select p.objid as oid from pg_depend p join nsp_oid n on refobjid = n.oid ), rew_oid as( select p.objid as oid from pg_depend p join dep_oid d on p.refobjid = d.oid where ...
You can use pg_depend table to resolve that : select objid::regclass from pg_depend where refobjid = (select oid from pg_namespace where nspname='mySchema');
What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?) You are going to have to drop the triggers and recreate them I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?) Here is ...
If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this: By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person ...
I see one possible design with 4 tables, (If I understand your question), These PK will be auto incremented Table Team TeamId (PK) ..Other Columns Table Player PlayerId (PK) TeamId (FK) ..Other Columns Table RoleType RoleId (PK) RoleName Table EmailRoleWise PlayerId (FK) RoleId (FK) EmailId
Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks ...
What I would suggest to you before putting a lot of work into designing a system, is to take a leaf from the book of the great Issac Newton - "If I have seen further it is by standing on the shoulders of giants". By this I mean take a long hard look at what's been done already - there are systems that could be of interest to you in this domain - take a ...
Neo4j is a schema-optional graph database. You could choose to enforce a full schema and reap the benefits.
Top 50 recent answers are included