mysql – Saving data and its change history and email history – Is there a problem with my database design?

background

I design some tables that allow data to be assigned to an object to track the progress of that object, as well as functions for storing emails sent in connection with those contracts and their data. Suppose the object is a contract (a contractual task to be performed).

That means we have a contract table

Contract Table

Now we can have date fields for this contract. For example, a received date, a start date, a review date, an estimated end date, an end date, and so on. Currently, most contracts contain the same columns, but we do not know in the future if there are any more columns to be added or less will be used Change requirements frequently.

For this reason, I have decided to separate the data from the contract table. Each date in this ContractDate table would have a foreign key to a FieldHeading table that describes what aspect of the contract is the date. And finally, an AuditTrail type table named ContractDateHistory that logs the changes to a date.

Contract and date tables are separated by a date history table.

In this way, we can also selectively select which data belongs to a contract. One contract can have 4 dates and another 6 dates.

With these contracts, emails can be sent to persons who inform about the expiration of this data or the change of a date. Because multiple contracts can be included in an email, I have a ContractsForEmail table that contains the ID of the email for each contract that is included in an email.

Contracts with e-mail tables

For each e-mail sent to a user, a line is entered. NeedsResolution is present when the user needs to take action with the email (depending on the email type), and resolved is present when the user has taken action. A program verifies that the user has taken action after a set period of time. Otherwise, the program will send a reminder e-mail (which may also appear in this table).

The template can contain a list of users to whom the emails derived from the template are sent (for each user assigned to the mailing list of a template, an email is sent, but additional users can be sent, An e-mail is sent here.

My question

I have many questions about whether my design is okay, but I would like to know what I do with contracts and contract appointments. Is it okay?

Should I simply add the "Date" columns to the "Contracts" table instead of separating "Contract" and "Date"?

If they get used to it, then it's good, and if they do not do it for a particular contract, it's just considered ZERO.

This can cause columns in the contract table to be added and never used again (almost always NULL). How so:

Contract table with dates inside

AddedDateInFuture is intended to illustrate that a column may be added in the future and no longer exists.

  • What are the trade-offs between the two options?
  • Is something missing in the email tables? Would this type of design work?

Currently, the system would serve fewer than 200 users, but I want it to be a little future-proof when used by many more users.

As a final note, some database tables, such as the usual "user" table, have been omitted.

mysql – Returns an ID specifying data that does NOT have the same category as the given id

I have 3 tables: video, video_category and video_category_join.

A video can have multiple video_category and a video_category can be associated with multiple videos: hence the video_category_join table

TABLE: Video

| video_id | title | description | Duration |
| --------------------------------------------
| 12 | hello | ... | 190 |
| 24 | hello | ... | 190 |
| 78 | hello | ... | 190 |
| 34 | hello | ... | 190 |
| 91 | hello | ... | 190 |
| 88 | hello | ... | 190 |

TABLE: video_category

| video_category_id | name |
| ------------------------------
| 4 | Music |
| 8 | tv |
| 5 | black |
| 2 | politically |
| 1 | Movie |
| 6 | others |

TABLE: video_category_join

| video_id | video_category_id |
| ------------------------------
| 24 | 4 |
| 24 | 5 |
| 88 | 1 |
| 91 | 6 |
| 91 | 5 |
| 78 | 4 |

Accepted video_idI want to select random videos that do NOT have the same category as the one specified video_id,

So for example, if the video_id At 24, the result set should return videos that do not match the same category as video_id 24. In this case, the only videos do not have the same category as video_id 24 are: video_id 88 and video_id 91st

This is a query that I thought might work, but if that is true video_id has no categories there is nothing back.

SELECT DISTINCT v.video_id, v.title, v.duration, 2 AS preferred
FROM video_category_join vc
JOIN video_category_join vc2 ON vc2.video_category_id <> vc.video_category_id AND vc2.video_id <> vc.video_id
JOIN video v ON v.video_id = vc2.video_id
WO vc.video_id = 5
ORDER BY EDGE ()
LIMIT 20

mysql – Edit the database ID in PHP

I'm trying to make a query to the database faster the edition, I explain: I have the following code "SELECT * from bdd where id = 17"
What I want is too ID Enter it via a form, as I have to update it manually every week.
There is a lot of data that I need to update at the same time, for example:

$ sqlp = "SELECT * from bdd where id = 17";
$ resultp = mysqli_query ($ connection, $ sqlp);
$ rowsp = mysqli_fetch_array ($ resultp);
if ($ rowsp) {
echo & # 39;<img height = "40px" src = "http://es.stackoverflow.com/escudos/.png ">& # 39 ;;
}

$ sqlp = "SELECT * from bdd where id = 7";
$ resultp = mysqli_query ($ connection, $ sqlp);
$ rowsp = mysqli_fetch_array ($ resultp);
if ($ rowsp) {
echo & # 39;<img height = "40px" src = "http://es.stackoverflow.com/escudos/.png ">& # 39 ;;
}

As you can see, there are two images, each referring to a different ID. As I said, what I am trying is "id" to enter it from a form:

mysql – how to search in one language and get results for another language

I have two tables

product

| ---- | --------------------- |
| id | Code |
| ---- | --------------------- |
| 1 | A |
| 2 | B |

and

product_tarnslation
| --- | ------ | --------------------- |
| pid | name | locale |
| --- | ------ | --------------------- |
| 1 | prod A | en |
| 2 | prod B | en |
| 1 | منتج | ar |
| 2 | prod B | ar |

I need a query to search in a locale, but only get the locale you want.
e.g:
if I search for prod and I will Arabic translation

Expected result
| --- | ------ | --------------------- |
| pid | name | locale |
| --- | ------ | --------------------- |
| 1 | منتج | ar |
| 2 | prod B | ar |

mysql – Left join finds missing relations

users table
ID, name

table connection

id, user_id, active

The user can have multiple connections with active fields from 0 to 3

When I leave the affiliate, I get the entire connection for each user. It's good.
What I'm looking for is how to find users who have few connections, but the missing number 1 in active, so it has few connections active 0, one active 2, but none active 1.

Any ideas???

mysql – Different results if the primary key index is ignored. Errors associated with summer time?

I hope you can help to explain this behavior, or try to reproduce the problem so that I can be sure to submit a bug report.

Essentially, I get different results from these queries:

# returns 0
Select count (*) as COUNT_WITH_INDEX
of a
where id = 1 and begin_time = & # 39; 2018-11-04 01: 01: 00.000 & # 39 ;;

# returns 1
Select count (*) as COUNT_WITHOUT_INDEX
from an ignore index (PRIMARY)
where id = 1 and begin_time = & # 39; 2018-11-04 01: 01: 00.000 & # 39 ;;

The main difference is the use of Ignore index (PRIMARY),

If you do not immediately notice the date, this date falls within the hour of daylight saving time for the "USA / Central" time zone. 01:01 clock occurred on 04.11.2018 twice. I have only encountered problems with timestamps falling into this window, so I suspect that it is a mistake in applying DST rules.

Regardless of whether or not I have to use convert_tz () In order to get the date I want correctly, there is still the fact that I get different results with and without it PRIMARY Key index.

Complete test case:

Create database, if not available test_dt;
use test_dt;

drop table if available a;

CREATE TABLE `a` (
`id` int (11) NOT NULL,
`begin_time` timestamp (3) NOT NULL DEFAULT '0000-00-00 00: 00: 00.000 & # 39 ;,
PRIMARY KEY (`id`,` begin_time`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;


set TIME_ZONE = & # 39; + 0: 00 & # 39 ;;

Paste into a value (1, # 2018-11-04 06: 01: 00.000 & # 39;);
Paste into a value (1, & # 39; 2018-11-04 07: 01: 00.000 & # 39;);

set TIME_ZONE = & # 39; US / Eastern & # 39 ;;

Choose * from a;

# returns 0
Select count (*) as COUNT_WITH_INDEX
of a
where id = 1 and begin_time = & # 39; 2018-11-04 01: 01: 00.000 & # 39 ;;

# returns 1
Select count (*) as COUNT_WITHOUT_INDEX
from an ignore index (PRIMARY)
where id = 1 and begin_time = & # 39; 2018-11-04 01: 01: 00.000 & # 39 ;;

set TIME_ZONE = & # 39; US / Central & # 39 ;;
# Repeat with Central if you want

Note: Install time zone data to use named time zones.

Tested under :: CentOS 7 with 5.6.36 and 5.6.43. I have no 5.7 or 8.0 installations at hand.

Another side note: The initial problem I encountered was that links between parent-child tables did not return data with compound PK (ID, timestamp). Since timestamps are stored in UTC format, I would not believe that DST data is a problem, but here I am.

Do you have an explanation for the behavior?
Do you think it is a mistake?

Many Thanks!

Populate MySQL Workbench 8.0 data with foreign keys

As you can see in the picture below, I have such information. The most important information in Regionen_andere will be the following (short):

NA AMS
LATAM AMS
APAC APJ

Since I will use the AMS / APJ format I have created regions Table to use and Regions others how maybe i'll need this information.

For example, the following question is when I start to fill in Regionen_andere Is there a way to populate them with the information in? regions, For example, I'm talking about data validation in Excel, when you say that only the values ​​from list X can be in the cell. Like the table regions is filled, could not I use it to fill the second table?

Enter image description here

scala – Connection to MySQL over 127.0.0.1 not possible

I'm trying to talk to MySQL on JDBC (in Scala), but there is an error

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communication connection error

The last packet successfully sent to the server was 0 milliseconds ago. The driver has not received any packages from the server.
at com.mysql.jdbc.Util.handleNewInstance (Util.java:408)
at com.mysql.jdbc.SQLError.createCommunicationsException (SQLError.java:1137)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:356)
at com.mysql.jdbc.ConnectionImpl.coreConnect (ConnectionImpl.java:2504)

I reviewed similar posts on Stack Overflow, but none of them helped

I think I have an idea of ​​what this could have caused, but can not find a solution.

I have started a MySQL server and can access and create tables, etc. using localhost

- mysql -hlocalhost -P3306 -uroot

However, if I use an IP, an error will occur

- mysql -h 127.0.0.1 -u root
ERROR 2003 (HY000): Unable to connect to the MySQL server at & # 39; 127.0.0.1 & # 39; to be produced (61)

I think that could be related.

Here's the code snippet I want to programmatically access.

    val url = "jdbc: mysql: // localhost: 3306 / mysql"
val driver = "com.mysql.jdbc.Driver"
val username = "root"
val password = "root"
Class.forName (driver)
Pressure ("level 0")

To attempt {
val connection = DriverManager.getConnection (url, "root", null)
}
Catch {catch
Case e: Exception => e.printStackTrace
}

Every help is appreciated.

MySQL INSERT IN … ON DUPLICATE KEY do nothing

MySQL INSERT IN … ON DUPLICATE KEY DO NOT DO | Web Hosting Talk

& # 39);
var sidebar_align = & right; & # 39 ;;
var content_container_margin = parseInt (& # 39; 350px & # 39;);
var sidebar_width = parseInt (& # 39; 330px & # 39;);
// ->

  1. MySQL INSERT IN … ON DUPLICATE KEY do nothing

    Is the only way to accomplish this something like ON DUPLICATE KEY UPDATE col1 = col1? That just does not look elegant.


Similar topics

  1. Reply: 16

    Last contribution: 06-04-2009, 16:44 clock

  2. Reply: 5

    Last contribution: 21/08/2005, 17:58

  3. Reply: 1

    Last contribution: 17.03.2005, 11:34 clock

  4. Reply: 3

    Last contribution: 18/02/2004, 19:26 clock

  5. Reply: 2

    Last contribution: 29/09/2003, 15:32 clock

Tags for this thread

Publish permissions

  • you not allowed post new topics
  • you not allowed Post answers
  • you not allowed Post attachments
  • you not allowed Edit your posts




DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123
Proxy Sites Proxy Tunnels Proxy List Working Proxy Sites Hotproxysite Proxy Sites Proxy Sites Anonymous Proxy Anonymous Proxies Top-Proxies.co.uk http://www.proxysitesnow.com Proxy Servers Free Proxies Free Proxy List Proxy List Zoxy Proxy List PR liste all proxy sites More Proxies netgofree netgofree Hide-MyIp - The Best Proxy List American Proxy List www.proxylisty.com/proxylist Web Proxy Submit Proxies Updated Proxy List Updated Proxy List aproxy.org Bypass Proxy Sites Free Proxies List Evolving Critic Business Web Directory Free Proxy List iShortIt MyProxyList Online Proxies Go Proxies Need Proxies PrivateProxies Proxies4MySchool Proxies4Work Free Proxy List Free Proxy Sites ProxyInside Wiksa Proxy ProxyLister.org Free Proxy List ProxyNoid Proxy List Free Proxy List Proxy Sites Proxy TopList ProxyVille UK Proxy WebProxy List RatedProxy.com - Listing the best Web Proxies Free Proxy List SchoolProxiesList Stay Anonymous Proxy List The Power Of Ninja Proxy List UNubstruct Free proxy sites Free proxy sites