Good practices for designing monthly subscription system in database
database,database-design,database-schema
After browsing a payment solution API like Stripe, I just figured out that my question is irrelevant because they handling this for you. In the Stripe case, the only mandatory thing you need to do is to link your back-end User class with the Stripe Customer class using foreign keys....
Do all tables need Primary keys or is Foreign key enough?
foreign-keys,primary-key,entity-relationship,database-schema,entities
It looks like the primary key of OutstandingProblem could be jobId if your Job cardinality of "1 to 1" is correct. ResourceType looks like it should have a composite primary key: (technicianID, resourceID). The cardinality "many to many" between ResourceType and Technician looks questionable. That would seem to imply that...
Organize table avoiding redundancy
database,database-design,logic,store,database-schema
You are interested in the predicate "[ID_Line] makes a stop at [Id_Stop] at time [Hour]". Table Make as defined will do to hold the rows that make that true. Its only candidate key (hence primary key) is (ID_Stop,ID_Line,Hour) since no other subset of columns is unique. Your diagram should include...
List all tables in cakePHP 3.x
php,cakephp,database-schema,cakephp-3.0
We can get list of table in cakephp3 using very similar pattern as $tables = ConnectionManager::get('default')->schemaCollection()->listTables(); ...
How to design relational database which contains companies and simple customers
mysql,database-design,database-schema
I'd start by restating the requirements in more precise language. A conference has many participants. A conference has many clients. A client may be an individual attendee, or a delegate from a company. All participants are clients. Clients place reservations for 0 or more conferences. A client of type "company"...
Grant all privileges to user on Oracle schema
You can do it in a loop and grant by dynamic SQL: BEGIN FOR aTab IN (SELECT table_name FROM all_tables WHERE owner = 'MY_SCHEMA') LOOP exectute IMMEDIATE 'GRANT ALL ON MY_SCHEMA.'||aTab.table_name||' TO MyUser'; END LOOP; END; ...
SQL Server 2012 - What is the purpose of assigning ownership of a schema to a user?
sql,sql-server,database-schema
An owner of a schema is like the sysadmin within that schema, can create , drop, select , update, delete , alter objects, give permissions to other users , revoker permissions of other users and pretty much everything . On the other hand a user with in a schema can...
Database Design Advice for a Social Network App Needed
database,database-design,database-connection,relational-database,database-schema
You will need to have one more table in order to create what is known as a many to many relationship between the users and the groups. Since you didn't specify the rdbms you are working with, I'll use SQL Server for my code: CREATE TABLE TblUserToGroup ( UserToGroup_UserId int...
Doctrine schema update or Doctrine migrations
symfony2,doctrine2,database-schema
When you are using the schema-tool, no history of database modification is kept, and in a production/staging environment this is a big downside. Let's assume you have a complicated database structure in a live project. And in the next changeset you have to alter the database somehow. For example, your...
DynamoDB schema for filtering users by attributes
database-schema,amazon-dynamodb
Let's look at the things we should avoid first so we can narrow down the options for the implementation: We should avoid table scans (whenever possible), querying by the primary key is always the best way We should avoid uneven access patterns, choosing a Hash Key containing values that are...
CakePHP 3.0 - Console does not contain Schema shell
shell,database-schema,cakephp-3.0
It was replaced by this official plugin: https://github.com/cakephp/migrations It offers a more robust solution to schema migrations and initial schema dumping....
Storing multiple item settings in database
mysql,database-design,database-schema
depends. are only programmers with pocket-protectors looking at data and app code? i am all for complexity but i would say spring for a little overhead. also, despite valiant efforts, i have failed to do bit-wise searching in mysql Problems with bit-wise searching, see this....
How to use embedded document structure for Many-to-Many relationship in mongoDB?Possible?
Based on official documentation are two options: dbRef manual references. dbRef Keep in mind that: To resolve DBRefs, your application must perform additional queries to return the referenced documents. Many drivers have helper methods that form the query for the DBRef automatically. The drivers 1 do not automatically resolve DBRefs...
Correct schema for multiple one to many relationships?
mysql,database-design,database-schema
Your approach is very generic. A product doesn't have any fixed attribute in your model (except its name), but has a set of optional associated attributes. So there can be trousers of unknown color and material. Or a computer made of cotton and trousers made of tin. An Xbox mirror...
How to merge more than 1 cell value with a comma in a single cell of a row with some restrictions
sql-server,stored-procedures,database-schema
Here you go! Complete script, just make sure to test really well for every possible case and don't forget to mark it as an answer if it fits your needs: IF OBJECT_ID('TEST1') IS NOT NULL DROP TABLE TEST1 GO CREATE TABLE TEST1 ( STUDENTID INT , ATTCODE VARCHAR(2) , ATTDATE...
Alter schema of Access 2013 database with linked table
ms-access,database-schema,linked-tables
Examine the TableDef properties for your linked table. Here is an Immediate window session which examines properties for a linked table named remote_Foo: ' Connect identifies the database source of the linked table ? CurrentDb.TableDefs("remote_Foo").Connect ;DATABASE=C:\share\Access\BigDb_secure.mdb ' SourceTableName is the remote table name ? CurrentDb.TableDefs("remote_Foo").SourceTableName tblFoo You can use Ctrl+g...
Schema Design for Invoices and Transaction - “mystery” relation
sql,sql-server,database-design,relational-database,database-schema
If a transaction can match many invoices, and an invoice can match many transactions, there is no direct way to "lock" the rows so that they cannot be used any more once already used. For example if you match 2 invoices to 3 transactions, each transaction will refer to 2...
Mongoose throws `Field is not in schema` error when defining a field twice
node.js,mongodb,mongoose,database-schema
This issue seems to be a bug in Mongoose. The issue is fixed and will be released in Mongoose 3.8.24 according to this link: https://github.com/LearnBoost/mongoose/issues/2665...
Best pratice to design this: enum in c# or separate database table
c#,database-design,database-schema
Depends on two things: Are values unstable?1 Do you need to attach additional information?2 If the answer to any of the above questions is "yes", then using a dedicated lookup table is probably a good idea. Otherwise, constant enum values3 that are well-known and well-documented throughout the system are OK....
First Normal Form
database-design,database-schema,database-normalization
Your solution is actually in 2nd Normal Form, because you moved the education data into a separate table. If you only wanted to be just 1st Normal Form, you could keep the education data in the employee table, but repeat all the employee information on the rows with different education,...
Copy SQL-Server Login Schema Problems
sql-server,database,database-schema
It is probably because the SIDs do not match anymore (due to orphaned users). Not sure what steps you tried, but when moving databases between servers, i would first reference MSFT's KB article 314546 - How to move databases between computers running SQL Server. There is a section that talks...
normalization of database structure
mysql,database,database-design,database-schema
I would use case one but with some changes. The parameter entity does hold one thing, parameters for a table. An instance of a parameter entry should relate to only one table (based on your analysis that they are not related). Parameter ---------- PK Param_ID FK Main_Table_ID Main_Table_name (A or...
Mongodb Architecture - How to design so I can quickly know what is available
performance,mongodb,database-design,database-schema,mongodb-query
If quickly identifying a user's unanswered polls is very high priority, you can do it by storing a document representing each pair (user, poll): { "user" : "Suzy Song", "poll" : "What shape Lego block are you? Take this poll and you'll never believe what happens next! Top 10 celebrity...
Continuous Integration, best practice to input actual test data into database, using Propel ORM
phpunit,continuous-integration,database-schema,propel
They say that "best practice" in anything at all doesn't exist - it's so subjective that one ought to settle for one of several forms of "good practice" instead. I think the below qualifies for that label — and ultimately it works well for me. I've been using PHPUnit for...
Followers - mongodb database design
javascript,mongodb,database-design,database-schema,database-performance
I agree with the general notion of other answers that this is a borderline relational problem. The key to MongoDB data models is write-heaviness, but that can be tricky for this use case, mostly because of the bookkeeping that would be required if you wanted to link users to items...
Messaging system flow into database
php,mysql,database-design,message,database-schema
Do I have to insert 2 records everytime a message was sent? (Sent and Inbox) No, just one time. Is that the best database schema to follow if I only need a Simple/personal message? I would recommend keeping it very simple. Note that "Sent_two" should obviously be "sent_to". This...
Database design - should two projects share the same table?
database,postgresql,database-design,relational-database,database-schema
Only one of those fk constraints works per column of the same instance of the table. You would have to add one column for each fk. Or have two documentstore tables. As you clarified, the same row in documentstore belongs to either a letter or an email, but only to...
integrity constraint violated - parent key not found even when row exists in parent table
mysql,sql,database,oracle,database-schema
Not sure why you have so many database tags in the question. In Oracle, check the columns and corresponding constraints properly. You can query user_constraints and user_cons_columns - SELECT * FROM user_constraints WHERE WHERE table_name='<your_table_name>'; SELECT * FROM user_cons_columns WHERE table_name='<your_table_name>'; Make sure the table_name is in UPPER CASE....
Database schema
What you can do is create a schema similar to the one below. Of course, you need to add additional columns to hold additional data if you have any and also adjust the queries with datatypes specific to the RDBMS you're using. CREATE TABLE Office(OfficeID integer , OfficeName VARCHAR(10)) CREATE...
Invalid object name 'product_Design'
asp.net,sql-server,database,database-schema
So finally i found what is the problem. if you ever face such kind of problem then execute this command in sql server and see whether your table is connected to any schema apart from dbo. Use this statement to check whether the table is connected to any other schema....
SQL bad attribute (column) names
mysql,database,database-schema
It's called a column, not an attribute ;) Don't use reserved words for column names. MySQL list here: https://dev.mysql.com/doc/refman/5.0/en/reserved-words.html . Multi-db list here: https://www.drupal.org/node/141051 If you want your SQL to be compatible with other databases, you should use lower snake_case ...
Storing items and groups of items
database,database-design,database-schema
Let's start with the relationships. An image can be in one or more groups. A group can have one or more images. So, now we create the domain tables. Image ----- Image ID File name Image size ... Group ----- Group ID Group name Group description ... Now, we create...
Laravel - Creating tables dynamically (without migration)
php,mysql,laravel,database-schema
Ugh, never mind... I worked on it for long enough, and the solution as always was... Very simple. I just had to figure a connection for the database first, so instead of Schema::create('tableName', function($table) { $table->increments('id'); }); It is Schema::connection('mysql')->create('tableName', function($table) { $table->increments('id'); }); Hope this helps someone someday in...
How can I organize this set of data into a mysql database?
mysql,database,database-schema
Here is rough design: (you might have to modify db according to your requirements) tblTeams ------------------------------ team_id | int | primary key team_name | varchar| not null tblPositions ----------------------------- position_id| int | primary key pos_name | varchar| tblPlayers ------------------------------ player_id | int | primary key player_name| varchar team_id | int...
dbunit schema issues with h2 and oracle, schema is always PUBLIC
oracle,database-schema,h2,dbunit,in-memory-database
If you want to create a schema x, and also set the default schema to x, in the database URL, then you need to use: jdbc:h2:~/data/databaseName;init=create schema if not exists x\;set schema x Please note the escaped semicolon. In Java, you need to escape the backslash as well: String url...
SQL table design for sports stats
sql,database-design,database-schema
In the interest of actually answering your question, and not just commenting on it, here are some additional thoughts. From your description this is how your tables look: Personally, I try to use natural keys as much as I can, but I can see in this case the Games table...
Designing a Database Schema - Data that is common to all users or custom made
ruby-on-rails,database,database-design,relational-database,database-schema
Try to not think in terms of tables first, but in terms of classes and the behavior of their instances. What would matter most in the end is not how many tables your design has, but whether the responsibilities have been correctly attributed to the different classes in your model....
Checking progress of MySQL schema change
mysql,database,schema,database-schema,alter
No, there's not much you can do to monitor progress of an ALTER TABLE statement, nor can we know how much time it has left to finish. You can use a tool like pt-online-schema-change, which does output an estimate of progress and time remaining, but more importantly it does not...
How to add new relations and making weight dependent in MySQL database?
mysql,database,database-design,relational-database,database-schema
The weight determines if a symptom/disease is common or less common. The common symptoms/diseases are listed above the less common symptoms/diseases. That is fine. A bit simplistic, but fine. That is ranking by weight or ORDER BY weight. Making weight in symptom_disease dependent on the selected age and gender...
how to resolve the database schema for the following
mysql,sql,database-design,relational-database,database-schema
In this case, there will be a table for each column in your table, where they refer back to their parent, i.e. Stream Table will have a UniversityId column referring the University table, etc. A bit simplistic, but a start. Look at the below links for now: http://www.dummies.com/how-to/content/knowing-just-enough-about-relational-databases.html and then:...
SQLAlchemy multi-schema support
python,python-2.7,sqlalchemy,database-schema
I was able to find a way to change the schema at runtime! I found the answer on the following post: sqlalchemy dynamic schema on entity at runtime...
How to design this tables better?
database,database-design,relational-database,database-schema
I am pasting in my comment as an answer so you can mark the question answered. You could break out ports to a separate, normalized table with deviceId, port number, and port name. You will have one record for each device and port combination with a foreign key reference back...
Keep collection in db and do a select query over records
mysql,database,database-schema
Should I create a services column in my users table If services is plural than it sounds like that column would store multiple values. That's no good. If that's the case then this is a many-to-many relationship, which involves a linking table. Something like this: Service ---------- ID etc....
MySQL transactions with schema modification statements (DDL)?
mysql,transactions,innodb,database-schema,ddl
BEGIN in some contexts is the same as START TRANSACTION. phpmyadmin performs one query at a time unless you put the batch in the window for such. Try that. (And SET autocommit = 0 is irrelevant because of the explicit START.) Edit Since the "transaction" really had a DDL statement,...
Mongodb: Can't append to array using string field name
javascript,node.js,mongodb,mongoose,database-schema
Everything @cbass said in his answer is correct, but since you don't have a unique identifier in your NetworkRequest element to target, you need to do it by position: var query = {'UserID': req.body.UserID}; var update = {$push:{'NetworkRequest.0.to': req.body.FriendID}}; Test.update(query, update, {upsert: true}, function(err, result) { ... }); 'NetworkRequest.0.to' identifies...
Updated rows not expected values with JOIN?
mysql,join,foreign-keys,sql-update,database-schema
UPDATE category_to_website JOIN websites ON websites.id = category_to_website.url_id JOIN main.websites ON websites.url = main.websites.url JOIN category ON category.name = main.websites.category1 SET category_to_website.category01_id = category.id SETs are done to rows that participate in the JOINs. But if you LEFT JOIN to category_to_website then all its rows participate so then you must...
Data Integrity importance when taking the NOSQL approach ?
mongodb,nosql,database-schema,software-engineering,data-integrity
MongoDB does not have any build-in features which ensure consistency (only exception: uniqueness constraints can be enforced with unique indexes). The responsibility to not write inconsistent data to the database is delegated to the application. When you have redundancy in your database schema (not as deadly a sin in MongoDB...
ER (Entity Relationship) Diagram == Relational Schema Database?
uml,schema,entity-relationship,database-schema,entity-relationship-model
There a few more steps. If you have a M:N (or in UML notation, .... ) association then you would need to create an intermediate table to store the links ("instances") of that association in the database as a result of the normalization process. You must also normalize generalization relationships,...
Normalizing a database schema
database-design,database-schema,normalization,database-normalization
As philipxy commented, describe the possible problems or concerns you may have. You defined one relationship for which you've not yet drawn a line (do so). Tables should be named in singular. You’ve mixed some singular, some plural. Indeed, you could do something different with answers. You may not be...
Unique usernames in 2 tables at different levels. Suitable database design?
database-design,relational-database,database-schema
I think that both David Aldridge and Ray More have given you potentially suitable design suggestions, but I'd like to answer your question a little more holistically, and with a concentration on your statement that "[you] wonder if [you're] making a database design mistake." Short version: Yes, I think you...
BCNF Decomposition?
database,database-design,relational-database,database-schema,bcnf
You have correctly identified the issue around which the problem revolves. That is, whether "BC->AE" indeed applies to ABCE or not. For it to apply to ABCE, it needs to be proved that it applies to the original, ABCDE. Can you prove that ? (Hint : start with B->D from...
Conceptualizing a parse.com data structure for checkout lanes in a store
ios,swift,parse.com,database-schema
Here's how I think it should work: Store class: holds information about all of the stores. Things like the number of registers, number of checkout lanes, location, etc. Stores get a randomly assigned identifier when they are set up. Register class: holds information about all the possible registers. As you...
Two separated tables vs One table with two collumns
sql-server,database,winforms,database-design,database-schema
Employees and visitors are both people. Specifically people that may (or may not) have an access card assigned to them. I would have one People table that has a foreign key relationship to the AccessCard table. If you only care about whether the person is an employee or visitor, but...
Table with custom fields
mysql,doctrine2,database-schema,entity-attribute-value
( I am not acquaint with doctrine2 ) Just an idea : Why not have a table to hold field values : CREATE TABLE field_values_table ( id int(11) NOT NULL AUTO_INCREMENT ,field_value_1 varchar(100) NOT NULL DEFAULT '' ,field_value_2 varchar(100) NOT NULL DEFAULT '' ,field_value_3 varchar(100) NOT NULL DEFAULT '' ,field_value_n...
The three schema of the database
database,database-design,relational-database,database-schema,entity-relationship
The Three-level ANSI-SPARC Architecture aka three schema approach: An external schema is the database (with metadata including constraints) as seen by some user, a view of the conceptual schema. The conceptual schema is the database (with metatdata including constraints) per se, for an enterprise. The physical schema is implementation. Typically...
Why is there a class with entity name plus an underscore for JPA entity class
java,hibernate,jpa,database-schema
This is your "Canonical Metamodel". It can be used with the JPA Criteria API, as described by the JPA 2.x spec (Section 6.2). To stop Eclipse from generating these classes: edit your Eclipse project properties > JPA > Canonical metamodel (JPA 2.0) > Source folder - set the folder's value...
Database relation
relational-database,database-schema
What about you put it into a relationship the connects Training_Exam and Student together? Student_Training_Exam(student_id, training_exam_id, score) This would tell you, which student attended which training_exam, and the score he achieved in that training exam....
Optional relationship in SQL Server — How to implement in SSMS Schema Designer
sql-server,ssms,database-schema,relationships,table-relationships
If you're asking how this can be done in the schema designer (DB Diagram) in SQL Management Studio, it's easy:- Right-click the table in the digram, choose 'Table View' -> 'Standard' In the 'Allow Nulls' column, set the optional Foreign Keys to 'Allow Nulls' Press Ctl + S to save...
Does this look like a proper database design?
database-design,relational-database,database-schema
There is no correct or right way to design a database. What you have here looks well thought out and could be a way to design your database for your needs. More importantly than answering if this is a proper database design (which anyone could give their opinion on and...
BoneCP, other connection pools and the ability to switch Schemas
connection-pooling,database-schema,multi-tenant,bonecp
I abondoned BoneCP for HikariCP. All needs are met.
Database schema for activity related to many entities [closed]
mysql,database,database-schema,crm
Like i see, there is only a decission to make. First and second possibility are good because the keep the consistency of the db. In my case i decided to use 1. possibility.
Stackoverflow Database design [closed]
mysql,sql,database-design,database-schema
To get a broad idea you can look at the Stack Exchange Data Explorer which allows you to query the database, it also has a database schema on the right. https://data.stackexchange.com/stackoverflow/query/new More information can be found here https://data.stackexchange.com/help Top User made Queries are here https://data.stackexchange.com/stackoverflow/queries Schema Breakdown Posts Id PostTypeId...
Having questions as field name in database design
mysql,database,database-design,database-schema
If I were you I'd avoid column names with special characters like ? in the table itself. Writing queries can get troublesome. But you can use column aliases if you want. For example, SELECT NOW() 'What time is it?', t.weatherNice 'Is the weather nice?' FROM table t ...
relational model versus logial data model
database-design,relational-database,database-schema
Conceptual Data Model: The term conceptual model is used to refer to models which are formed after a conceptualization process in the mind. Conceptual models represent human intentions or semantics. Conceptualization from observation of physical existence and conceptual modeling are the necessary means human employ to think and solve problems....
How to set up Primary Keys in a Relation?
database,database-design,relational-database,database-schema,entity-relationship
Position Any practice that is not based on solid theory is not worthy of consideration. I am a strict Relational Model practitioner, with a strong grounding in the theory. The Relational Model is based on solid theory, and has never been refuted 1. There is nothing solid in what passes...
Visual Studio 2013 Schema Compare/Publish ignores column order in definition of foreign key constraint
sql,sql-server,visual-studio-2013,database-schema,database-project
Yes, the issue is a bug with SQL Server Data Tools (SSDT). Microsoft will include the fix in their next SSDT refresh on VS2012, VS2013, and the RTM of the next Visual Studio version. See link for more information: Visual Studio 2013/SQL Server Data Tools: Schema Compare/Publish ignores column order...
Synchronizing XML file to MySQL database
mysql,sql,xml,synchronization,database-schema
The way that I would approach the problem in your case is: Create a respective set of corresponding tables in the database which in turn will represent the company's Product model by extracting the modelling from your given XML. Create and use a scheduled daily synchronization job, that probably will...
How to link two relational tables in mySQL
mysql,sql,relational-database,database-schema
This should work: CREATE TABLE `video_tags` ( `tag_id` INT NOT NULL, `tag_name` VARCHAR(50) NOT NULL, `video_id` INT NOT NULL, PRIMARY KEY (`tag_id`), INDEX `video_id` (`video_id` ASC), INDEX `tag_name` (`tag_name` ASC), CONSTRAINT `video_id_fk` FOREIGN KEY (`video_id`) REFERENCES `video` (`v_id`)) Use another column as primary key...
scala-activerecord: how to pass custom configuration to Tables.initialize?
scala,activerecord,config,database-schema,typesafe
How can I pass my config to the Tables.initialize method? This might be a solution: import scala.collection.JavaConversions._ Tables.initialize(ConfigFactory.load(env).root.unwrapped.toMap) For ActiveRecordTables#initialize method, it is assumed that you give the override settings and values as follows: Tables.initialize(Map( "driver" -> "org.postgresql.Driver", "jdbcurl" -> "jdbc:postgresql://hostname:5432/dbname" )) This feature is supposed to be used...
MYSQL Best way to break huge data over time?
mysql,database,database-schema
If you generally only want to look at data in the past day, then my suggestion is to partition the data by day. You can learn more about MySQL partitioning here. Depending on the queries, you will also want indexes on the date and device. With 28.8 million rows per...
Ignoring DEFAULT constraints when comparing in Aqua data studio
sql-server-2012,compare,database-schema
With Aqua Data Studio v14, a new option is introduced which can take care of auto-generated constraint names. You can disable this checkbox and the schema compare tool will ignore the differences. This feature is however not possible in ADS v6.5.9 Uncheck that option and now you can compare the...
Populate treeview with user created tables, and their columns
c#,.net,treeview,database-schema
There are two problems in your code. The GetSchema returns only the table details of the database. That information does not contain column details. To filter out system tables you have to use the other overload of GetSchema(). You can specify the table type filter criteria. "TABLE" in the restrictions...
Write a query that select all events ordered by a particular customer in SQL [closed]
The table's name is events?... Select * from events order by custumers asc if not select events from Table order by custumers asc...
Why does a PostgreSQL SELECT query return different results when a schema name is specified?
postgresql,database-schema,postgresql-9.2,database-permissions,search-path
Table names are not unique within a database in Postgres. There can be any number of tables named 'table_name' in different schemas - including the temporary schema, which always comes first unless you explicitly list it after other schemas in the search_path. Obviously, there are multiple tables named table_name. You...
Should this information be calculated in real time or stored in a seperate database?
database,database-design,database-schema
This is a generic question about optimization by caching. The following was my answer to essentially the same question. Even though that question provided a bunch of different details, none of them were specific enough to merit a non-generic answer either: The more you want to calculate at query time,...
SQL Table Schema For Sub Product Types
sql,sql-server,entity-framework,database-schema
Your schema is close, but not quite there, you do need the link from dbo.NicheProduct.SupplierID to dbo.NicheSupplier.SupplierID, but you also need to add the link from dbo.Product to dbo.NicheProduct, to ensure that the combination of supplier and product id in NicheSupplier is a valid combination. To do this you need...
Mongodb schema design validation
I would create collections dedicated to tasks and responses. It will require you to write some 'relational plumbing' logic on the server-side. That's the price to pay for using MongoDB. Some libraries provide helpers to assist you in this (such as Mongoose's populate operator). Having a collections graph with a...
How to see which SQL Columns are groupable
sql-server,group-by,database-schema,dynamic-columns,aggregates
I don't think there's an easy way of doing this, e.g. querying the schema info directly for an IsGroupable property. This therefore feels like a mild hack, but here goes: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('bigint', 'bit', 'char', 'date', 'datetime', 'datetime2' , 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'nchar',...
Normalization in MongoDB
It all depends on how the end-user will interact with your application. From what you have commented up until now, I understand that your queries will most likely fall into one of the below categories: Bring all items from category X Bring all items from subcategory X Bring all items...
Generate jOOQ classes from pure Java
java,code-generation,database-schema,ddl,jooq
Jooq needs some tables to be created before hand. You can use Flyway for that (and you should use its migrations too). Once you have all your tables, you can use this snippet to have Jooq generate the source files for your tables: import org.jooq.util.GenerationTool; import org.jooq.util.jaxb.*; Configuration configuration =...
Is CQL limiting Cassandra from being 'schema free'?
nosql,cassandra,database-schema
For documentation you will most likely find more up to date information at the Datastax Website as there is a paid documentation team constantly working on Cassandra Docs. Datastax C* Docs The simplest solution for having arbitrary information in a row would be to use a MAP, LIST, or SET...
SQL Server for 1:0..1, FK on PK or PK + unique non-clustered index on FK?
sql,sql-server,database,sql-server-2012,database-schema
Short Answer: I suspect having the entire child table as a single B-Tree is likely to be the most efficient configuration (i.e. PK and FK on the same field, with clustered index). In other words, your 1st solution looks OK. Long(er) Answer: If by "fragmentation" you mean difference between logical...
Messaging table performance - Merge from and to id`s into one single field vs separate 2 fields
mysql,performance,database-design,relational-database,database-schema
What happens when you start getting more than single digit IDs? What does 1111 equate to? 1 to 111, 111 to 1 or 11 to 11? Stick with individual fields. It will be easier to interpret, manage and scale. ...
Oracle SOA suite domain error
jdbc,oracle11g,database-schema,oracle-fusion-middleware,soa-suite
Assuming you mean the schema username you created when you ran the Repository Creation Utility (RCU), then this name must be used in the domain creation screen. For ex: I am installing FMW 11.7 and 11.8. I will create two different schemas using RCU: DEV7 and DEV8. When I run...
database schema one column entry references many rows from another table
mysql,database,database-design,relational-database,database-schema
Why not create a Relationship table? You can create a table named Workorders_Parts with the following content: |workorderId, partId| So when you want to get all parts from a specific workorder you just type: select p.name from parts p inner join workorders_parts wp on wp.partId = p.partId where wp.workorderId =...
Tasks + participants: JOIN n..n and divide the result on groups (in 1 query)
mysql,sql,database,database-schema
Did it this way in Laravel v4.2. Number of queries = 6 * number of tasks. Heavy.. but I will change it soon. /** * Get the full list of tasks with participants * @return array */ public static function index() { /** * All tasks * @var array */...
Linking an address table to multiple other tables
sql-server,database-design,database-schema
I will suggest using Idea one and preventing Idea two. Second Idea is called Polymorphic Association anti pattern Objective: Reference Multiple Parents Resulting side effect: Using dual-purpose foreign key will violating first normal form (atomic issue), loosing referential integrity Solution: Simplify the Relationship The simplification of the relationship could be...
Database Constraints
database,database-design,uml,relational-database,database-schema
Two different contracts for a player do not overlap if and only if one starts after the other finishes. So they overlap if and only if NOT(one starts after the other finishes). The constraint you want is that no rows of contractID pairs satisfy the condition that they overlap: CONTRACT(c1.contractID,...
Multiple correlated SQL queries
sql,select,join,tuples,database-schema
Show me the products that are used on every Project in Athens is equivalent to Show me the products where there is no project in Athens that does not use that product. This last sentence is exactly what the query above says. SELECT p.pname -- Show me the products FROM...
Setting schema name for DbContext
c#,entity-framework,entity-framework-6,database-schema
You can configure the default schema in OnModelCreating method of your custom inherited DbContext class like - public class MyContext: DbContext { public MyContext(): base("MyContext") { } public DbSet<Student> Students { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { //Configure default schema modelBuilder.HasDefaultSchema("Ordering"); } } Starting with EF6 you...
Relational database schema: multiple contributor tables, multiple contribution tables
database,ms-access,ms-access-2007,database-schema,jet
Your solution will require minimum changes, however you won't be able to keep referential integrity, since referential integrity won't let you have a record in Pledges, InKindDonations, and MonetaryContributions unless you have a FK value in both Organizations and Constituents for every record. For example if you set referential integrity...