Menu
  • HOME
  • TAGS

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...

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...

How to use embedded document structure for Many-to-Many relationship in mongoDB?Possible?

mongodb,nosql,database-schema

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...

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...

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...

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...

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...

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...

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....

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....

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....

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 */...

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...

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...

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...

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...

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...

Database tables for tennis court booking system

mysql,uml,database-schema,class-diagram

Does not look too bad. You already have classes for the actors. So that's done. The "privileges" can be modeled by assigning the specialized classes the according methods. That is: Owner has a createClub() method and Player has book() etc. You need a Booking class that keeps track of the...

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...

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...

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...

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...

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....

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',...

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...

Normalization in MongoDB

mongodb,database-schema

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...

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...

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...

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...

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...

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(); ...

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...

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 ...

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 =...

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...

Percona Toolkit Online Schema Change: Possible to add multiple columns all at once?

mysql,sql,database-schema,percona

According to the manual this is possible, just separate the statements with commas. Quoting the --alter section of the manual: --alter type: string The schema modification, without the ALTER TABLE keywords. You can perform multiple modifications to the table by specifying them with commas. Please refer to the MySQL manual...

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...

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...

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,...

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,...

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...

What is the relationship between schema and service_name in oracle?

database,oracle,database-schema

The service is one of the ways the connection can differ between different databases on the same machine/server/computer. In theory, you could not mention this and still get on the correct database (if there is only 1 database), but in context of database connection, all parameters need to be explicitally...

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,...

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...

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....

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...

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...

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...

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...

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 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"...

Mongodb schema design validation

mongodb,nosql,database-schema

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...

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...

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....

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.

MySQL use JOIN or store directly

php,mysql,performance,join,database-schema

The accepted practice is to utilize the JOIN statement and not have username within the posts table. To do so would violate normal form and result in redundant data. Also - what happens if the username changes? Then you have two tables (users, posts) that no longer match up because...

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...

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...

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...

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...

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....

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...

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...

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 - 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...

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...

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...

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 =...

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....

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...

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...

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...

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:...

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...

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...

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. ...

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...

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...

Write a query that select all events ordered by a particular customer in SQL [closed]

sql,database,database-schema

The table's name is events?... Select * from events order by custumers asc if not select events from Table order by custumers asc...

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....

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....

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...

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,...

Database schema

sql,database,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...

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...

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 ...

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...

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...

Grant all privileges to user on Oracle schema

oracle,database-schema,grant

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; ...

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.