NotionCommotion

Thanks Barand, Do you agree with Zane's assessment?

Correct, no service record. I suppose I could do so especially if it simplifies the schema and implementation, but currently don't have such a requirement.

Yes infamous and abstract and maybe unnecessarily so. I hesitated to make it so, but also was concerned about either adding too much minutiae and wasting other's time or not providing enough so one couldn't understand why I am doing such. An AbstractDataSource provides time based physical data and uses one of several means to obtain the data. A GatewayDataSource extends this class and utilizes a hardware gateway on a foreign network to obtain the data. There are several types of Gateways (bacnet, dali, modbus, opc, etc) which extend AbstractGateway, and there is a one-to-one relationship between GatewayDataSource and a gateway. Some of these gateways use a Device which both reflects other 3rd party controllers on the foreign network as well as the gateway itself, and I have classes 3rdPartyDevice and GatewayDevice which extend Device. There is a one-to-one relationship between the gateway and the gatewayDevice. A dataSource associated with a gateway which is associated with a device can be deleted, however, any associated 3rdPartyDevices must not be deleted but only tagged as deleted so that once its auto-increment PK is generated, it will always remain and reused should a device with specific natural key identifiers later be reinstated. To repeat the question with more context, should a foreign key be placed in GatewayDataSource or AbstractGateway? The same question applies to a gateway and a device.

Negative. A provision must be provided to replace a motor.

I truly appreciate your unequivocal advise. Do you feel that cascade delete using the DB should sometimes/often not be used?

I can create a one-to-one relationship between two tables by placing a unique constraint on the foreign key and making it NOT NULL, and make it one-to-at-most-one by removing the NOT NULL constraint. My question is what criteria should one use to determine which table holds the foreign key? For instance, I have the Car and Motor entities as shown below where one Car has one Motor, and conversely one Motor is used by one Car. AbstractPart - id (Auto Inc) - discriminator (car, motor, etc) - serialNumber (unique for given discriminator) - manufacturerId - weight - cost - countryOfOrigin - etc Car extends AbstractPart Motor extends AbstractPart What reasoning should one use to determine whether the Car table contain the Motor ID or should the Motor table contain the Car ID?

The network_node could also be uniquely identified by a composite natural key, however, I've finally came to the conclusion that big old composite keys usually don't end well and elected to use a surrogate. Furthermore, network_node.id is being used by another non-SQL application as a natural key. If a network is deleted or a network_node is tagged as deleted, and then later a network_node is added with that composite natural key I described, then this network_node needs to utilize the same PK so it may be recognized by the non-SQL application. Maybe way to complicated and a better way..

Thanks again, The following works perfect. If you feel I should be doing differently, please advise, and otherwise I am good to go. CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB; CREATE TABLE `network` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `account_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_name` (`name`,`account_id`), UNIQUE KEY `unique_pk` (`id`,`account_id`), KEY `fk_network_account_idx` (`account_id`), CONSTRAINT `fk_network_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB; CREATE TABLE `network_node` ( `id` int(11) NOT NULL AUTO_INCREMENT, `network_id` int(11) DEFAULT NULL, `account_id` int(11) NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), KEY `fk_network_node_network1_idx` (`network_id`), KEY `fk_network_node_account1_idx` (`account_id`), KEY `fk_shared_idx` (`network_id`,`account_id`), UNIQUE KEY `unique_name` (`network_id`, `name`), CONSTRAINT `fk_network_node_account1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_network_node_network1` FOREIGN KEY (`network_id`) REFERENCES `network` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION, CONSTRAINT `fk_shared` FOREIGN KEY (`network_id`, `account_id`) REFERENCES `network` (`id`, `account_id`) ) ENGINE=InnoDB;

You just taught me something. I never realized foreign keys could be used on a given column to multiple tables. I also never realized that ON DELETE and ON UPDATE can not be defined (PS. while it is true, reverse engineering into MySQL Workbench displays it as RESTRICT, but won't really change it to RESTRICT). Thanks!!! Do you mean something like the create statement at the bottom? How will this work regarding network_has_account? If a given network was deleted, then network_has_account would have NULL for one column of the composite primary key which surely isn't valid (and just to be sure, I tried doing so and of course couldn't even create the table). I guess I can add a surrogate PK instead of the composite PK but that doesn't make sense.... Oh... You really mean get rid of network_has_account have a FK to network.id and network.account_id. Humm, that won't work because account_id isn't part of network's PK. Maybe I make account_id as part of network's PK? Oh, then I am back where I started where I am trying to make network's composite PK allow NULL in one of its columns... While I am glad I learned more about foreign constraints, maybe I am going down an XY rabbit hole. Let me start over with high level requirements: network has one account, and account can have zero or many networks. If an account is deleted, all its networks should be deleted. network_node has zero or one network, and network can have zero or many network_nodes. Note that when adding a new network_node, the application will always ensure that it has a network, so I guess I should have said "network can have one or many network_nodes", but maybe the DB doesn't need to enforce. A network_node's network can be changed but only to another network belonging to the same account. I must be able to identify all network_nodes regardless of whether they have a network on a per account basis, and if the account is deleted, all associated network_nodes should be deleted. How would you implement this? Thank you EDIT. Oh, just put a unique constraint on network id/account_id so then I can have a FK from network_node network_id/account_id to network id/account_id? CREATE TABLE IF NOT EXISTS `mydb`.`network_node` ( `id` INT NOT NULL, `network_id` INT NULL, `account_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_network_node_type2_network_has_account1_idx` (`network_id` ASC, `account_id` ASC), INDEX `fk_account_only_idx` (`account_id` ASC), CONSTRAINT `fk_account_network_match` FOREIGN KEY (`network_id` , `account_id`) REFERENCES `mydb`.`network_has_account` (`network_id` , `account_id`), CONSTRAINT `fk_network_only` FOREIGN KEY (`network_id`) REFERENCES `mydb`.`network` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION, CONSTRAINT `fk_account_only` FOREIGN KEY (`account_id`) REFERENCES `mydb`.`account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;

PS. Please disregard all those stupid duplicated ERDs at the bottom of my last post. Thought they were deleted but evidently weren't.

Maybe I misunderstand. I knew that unique constraint of one or more column where at least one column value is NULL is never considered a duplicate because NULL is, well, NULL. But I was still getting the error: errno: 150 "Foreign key constraint is incorrectly formed" So, I tried using MySQL Workbench to create the same structure. And then I went to make the foreign key on network_node ON DELETE CASCADE for network_id to ON DELETE SET NULL for account_id, and as seen below it did not give me the option to do so. Maybe not possible? Not the (maybe only) cause of the error, however, and I am still getting this weird "Incorrect options in FOREIGN KEY" error when changing a presumably valid ON DELETE from CASCADE to SET NULL. Note sure, but think it has something to do with a given foreign constraint in one table conflicting with a foreign constraint in another table. Have you come across this "Incorrect options..." error before? -- MySQL Script generated by MySQL Workbench -- Thu Oct 31 15:29:53 2019 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`account` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`account` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `name_UNIQUE` (`name` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`network` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`network` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `account_id` INT NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `name_UNIQUE` (`name` ASC), INDEX `fk_network_account_idx` (`account_id` ASC), CONSTRAINT `fk_network_account` FOREIGN KEY (`account_id`) REFERENCES `mydb`.`account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`network_has_account` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`network_has_account` ( `network_id` INT NULL, `account_id` INT NOT NULL, PRIMARY KEY (`network_id`, `account_id`), INDEX `fk_network_has_account_account1_idx` (`account_id` ASC), INDEX `fk_network_has_account_network1_idx` (`network_id` ASC), CONSTRAINT `fk_network_has_account_network1` FOREIGN KEY (`network_id`) REFERENCES `mydb`.`network` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION, CONSTRAINT `fk_network_has_account_account1` FOREIGN KEY (`account_id`) REFERENCES `mydb`.`account` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`network_node` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`network_node` ( `id` INT NOT NULL, `network_id` INT NOT NULL, `account_id` INT NOT NULL, PRIMARY KEY (`id`), INDEX `fk_network_node_type2_network_has_account1_idx` (`network_id` ASC, `account_id` ASC), CONSTRAINT `fk_network_node_network_has_account1` FOREIGN KEY (`network_id` , `account_id`) REFERENCES `mydb`.`network_has_account` (`network_id` , `account_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Thanks requinix, a) The networks cannot be added back, but only the network_nodes (and other records linked only to the network_nodes). Each account can have many networks and many network_nodes, and maybe I should have named "network" as "virtual_network" and "network_node" as "computer". The network ID is an auto-increment surrogate, and if a given network is deleted, then it is gone for good, but not the network_nodes, and network_node.network_id will cascade to NULL. The network_nodes, however, must still be tied to the account so that the application can allow a user to view only those network_nodes for their account (via network_node.account_id) which are not tied to a given network (i.e. network_node.network_id IS NULL), and add them back to another existing network should they desire. b) I was originally considering doing so, but it adds complexity due to requirements which I didn't initially provide. For a given account, network.name must be unique so I will need to add some "deleted_name" field and move the name to this field. This seems easy enough, and while I've haven't heard of others doing this approach, expect it is a pretty standard approach, agree? What is a bigger problem is other tables have a unique constraint on network_id and a user defined natural key. Given this requirement, recommend not using this approach? Maybe I will need to just use the applicable to ensure that network and network_node are both tied to a common account, but it is always nice to use the database if reasonable simple to do. Thanks

I have the following three tables. If network is deleted, network_node should not be deleted but remain an orphan which potentially will be added back. Is there any way to enforce by the database that network_node.network_id and network_node_account_id are both associated with the same account? I tried creating a forth table which had a composite key network_id and account_id where network_id was cascade set null, and then had network_node reference that table, but I get errno: 150 "Foreign key constraint is incorrectly formed". Is this not possible? Thanks account id name network id account_id (FK on delete cascade) name network_node id network_id (FK on delete set null) account_id (FK on delete cascade)

I have the following tables: department id course id department_id CASCADE DELETE student id student_courses course_id CASCADE DELETE student_id CASCADE DELETE I also have the following REST API endpoints: DELETE department/123 (deletes targeted department and associated courses as well as the list of students in the course) DELETE course/321 (deletes targeted course as well as the list of students in the course) DELETE student/111 (deletes targeted student as well as the list of students in the course) The user should be informed before deleting a given record if that record will affect other tables. For instance, if deleting a department which has some courses or deleting a student which belows to a course. One option is to respond to DELETE department/123 with 400 {recordUsed: {courses:[bla, bla], student_courses:[bla, bla]}} if used. For this option, the client would then prompt the user of the implications and if desired repeat the request but include a "force" parameter of TRUE in the request which will delete the record regardless of being used. Another option is to add some new endpoints which will return the resources which are associated with the to be deleted entity. If empty, a DELETE request would automatically be made and if not empty the user will first be prompted whether the DELETE request should be made and the server would delete the record(s) regardless of being used. GET department/utilized/123 GET course/utilized/321 GET student/utilized/111 Any thoughts on which approach should be used? Or maybe some other strategy? Thanks

Sounds pretty reasonable to me. If you want, you can add JS hoover preview.

My IDE (Nusphere's PhpED) runs on Windows but is configured so that both the web server as well as all project files are located on a development Linux server. Since the project files are located on the Linux server, the git repository is located there as well and not on my PC. Every now again, my IDE complains that content was externally modified and whether I wish to reload the file. Also, I was recently asked to set git's core.autocrlf to true on a project I was collaborating on which prevented me from adding any changes to git (git complained that LF will be replaced by CRLF). The IDE allows file encoding to be changed (system default, UTF-8, Windows-1252, or ISO-8859-01) as well as the default file format (Unix, Windows, Mac, or autodetect). Any recommendations on how best to configure git and these ide settings? Thanks

I see now. I only experience the issue when the file includes declare(strict_types=1) which is what it should do. Thanks

Is it possible to perform type declaration which allows either an integer or a string digit (i.e. ctype-digit) instead of the following? function someFunction(int $id){} Looking at functions.arguments.type-declaration, I expect not. If not, would you recommend type casting before calling the function or removing type declaration from the function and performing the check manually within the function and throwing an applicable exception? Thanks

You could POST the following. Be sure to validate. Array ( [text1] => foo [text2] => bar [otherField] => bla [fields] => Array ( [0] => text1 [1] => text2 ) )

Not complete, but I definitely think I am on the right path. Appreciate the help. I am sure I am not doing the discriminator part correct, and if anyone has experience, would appreciate some additional direction. <?xml version="1.0" encoding="UTF-8" ?> <serializer> <class name="NotionCommotion\App\Domain\Entity\Chart\Chart" discriminator-field-name="dtype"> <xml-namespace prefix="atom" uri="http://www.w3.org/2005/Atom"/> <discriminator-class value="pie_chart">NotionCommotion\App\Domain\Entity\Chart\Pie\PieChart</discriminator-class> <discriminator-groups> <group>index</group> <group>detail</group> </discriminator-groups> <property name="account" exclude="true"/> <property name="id" exclude="true"/> <property name="name" groups="index, detail"/> <property name="id_public" serialized-name="id" groups="index, detail"/> <property name="series" groups="detail"/> <property name="dtype" exclude="true"/> </class> </serializer> <?xml version="1.0" encoding="UTF-8" ?> <serializer> <class name="NotionCommotion\App\Domain\Entity\Chart\Pie\PieChart" discriminator-field-name="dtype"> <xml-namespace prefix="atom" uri="http://www.w3.org/2005/Atom"/> <discriminator-class value="pie_chart">NotionCommotion\App\Domain\Entity\Chart\Pie\PieChart</discriminator-class> <discriminator-groups> <group>index</group> <group>detail</group> </discriminator-groups> <property name="dtype" exclude="true"/> </class> </serializer> <?php class ChartService { protected $em, $account; public function __construct(\Doctrine\ORM\EntityManager $em, \NotionCommotion\App\Domain\Entity\Account\Account $account) { $this->em = $em; $this->account = $account; } public function read(int $idPublic):Chart\Chart { return $this->em->getRepository(Chart\Chart::class)->findOneBy(['id_public'=>$idPublic, 'account'=>$this->account]); } } <?php class ChartResponderr { protected $serializer; public function __construct(\JMS\Serializer\Serializer $serializer) { $this->serializer = $serializer; } public function detail(Response $response, Entity\object $entity):Response { $json=$this->serializer->serialize($entity, 'json', SerializationContext::create()->setGroups(['Default','detail'])); $response->getBody()->write($json); return $response; } } <?php $c['serializer'] = function ($c) { return \JMS\Serializer\SerializerBuilder::create() ->setCacheDir(APP_ROOT.'/var/serializer/cache') ->setDebug(true) ->addMetadataDir(APP_ROOT.'/config/serializer') ->build(); }; $c['chartService'] = function ($c) { return new Api\Chart\ChartService( $c[EntityManager::class], $c['account'] //$c['validator'](['/chart/base.json']) ); }; $app->get('/chart/{chartId:[0-9]+}', function (Request $request, Response $response, $args) { return $this->chartResponder->detail($response, $this->chartService->read((int) $args['chartId'])); });

No, it is not because I am using XML metadata for my entities, but because my objects are too large and/or have too much recursion and PHP times out after 20+ seconds. Looks like I need to figure out how to instruct the serializer only to include the specific properties which I want.

Thanks gizmola, My model example very closely describes mine, and I think it works great for all the reasons you state. My only issue is determine how to act on an entity when the entity type is not known. For instance, it would be easy to serialize an array of vendorStreams or an array of channelStream, but not necessarily so for an array of streams which contain both vendorStreams and channelStream. While I was thinking of making the entities implement an interface which requires a method for each desired output, but it really doesn't belong there for multiple reasons which I finally understand. Maybe would be okay to making the entities implement an interface for one method which requires an argument which determines the serialization output. Or better yet, maybe your serializer suggestion is the answer. No, that was not my error and I of course tried it. I am pretty sure it is because my entities do not have annotation in the PHP class but in a separate XML file. Looking for documentation how to implement. If so, then the serialization specification is tied to the entity making the ability to serialize an array of mixed entities simple, yet does so in a way to make it more flexible that just adding individual serialization type methods to each entity. Well, at least I hope so!

Read gw1500se's post a couple times. Not necessarily the part about not being clear but the part about each operating in their own environment. Draw two circles and put Client (HTML/JavaScript) in one and Server (PHP) in the other. Then make an arrow from the client circle to the server circle and label it "request for HTML page" and then a return arrow and label it "HTML page". Then maybe make an arrow from the client to the server and label it "form submission" and an arrow back labeled "form results". You can do this for Ajax request as well. For all of these, the client initiates a request and the server responds. Do it until it is 100% clear in your head.

Your not going to get the results you are looking for. The server will not know whether the user submitted the form using the submit button or clicked the back button which caused it to be submitted. There are most likely better ways to do this, but something like the following might work. <?php session_start(); function display($counter) { $_SESSION["counter"]=$_SESSION["counter"]+1; //create your page and include $counter in a hidden input field. } if(!isset($_SESSION["counter"])) { $_SESSION["counter"]=0; } if(isset($_POST["SubmitButton"])) { if(isset($_POST["counter"])) { if($_POST["counter"]==$_SESSION["counter"]) { //Process your order } else { display(); } } else { echo('missing counter error'); } } else { display(); }

What is best is based on what you want your script to do. That being said, I would probably start with the session conditional first. if(isset($_SESSION["order_is_finalized"])){ if(isset($_POST["SubmitButton"])) { $_SESSION["order_is_finalized"]=null; } else { include('order_page.php'); exit; } }