Legacy stargate loads already created portals on startup by checking attributes such as the facing of the portal, its gatedesign file (and its flip?).

Legacy then loads the type of gatedesign of the portal based out of those properties without looking at the surrounding structure.

This is not done in the complete rewrite version; as we don't even save a those properties to the database [flip,gatedesign,facing].

The purposed change to the database would be to change the local portals table to this:

CREATE TABLE IF NOT EXISTS {Portal} 
(name NVARCHAR(180), network NVARCHAR(180), destination NVARCHAR(180), 
world NVARCHAR(255) NOT NULL, x INTEGER, y INTEGER, z INTEGER, ownerUUID 
VARCHAR(36), gatefileName NVARCHAR(255), facing INTEGER, zFlip BOOLEAN,
PRIMARY KEY(name, network));

And for the interserver database:

CREATE TABLE IF NOT EXISTS {Portal} 
(name NVARCHAR(180), network NVARCHAR(180), destination NVARCHAR(180), 
world NVARCHAR(255) NOT NULL, x INTEGER, y INTEGER, z INTEGER, ownerUUID 
VARCHAR(36), gatefileName NVARCHAR(255), facing INTEGER, zFlip BOOLEAN, 
isOnline BOOLEAN, homeServerId VARCHAR(36),
PRIMARY KEY(name, network));

The portal location should no longer be logged from the sign position, but rather the signs "topleft". Or in other words, the location of the signformats topleft in relation to the orientation of the Gate

Found in commit 19b9b97 it's almost impossible for some of the unit tests to work properly if you don't store the location of a sign and the location of a button. It's also less complicated of a solution.

It might therefore be a good idea as @EpicKnarvik97 said to store those positions as well. My thought about this would be to add another table and have all positional data there. Something like this maybe (I am not good at sql):

CREATE TABLE IF NOT EXIST portalPosition
(portalName NVARCHAR(180),  networkName NVARCHAR(180), x INTEGER, y INTEGER, z, INTEGER,
 positionType NVARCHAR(8), PRIMARY KEY(portalName ,networkName ,positionType ));

PositionType would be strings like "topleft", "button","sign", "other custom type of control".

CREATE TABLE IF NOT EXIST portalPosition
(portalName NVARCHAR(180),  networkName NVARCHAR(180), x INTEGER, y INTEGER, z, INTEGER,
 positionType NVARCHAR(8), PRIMARY KEY(portalName ,networkName ,positionType ));

You don't need to save three values for the button and the sign, as you can instantly get the actual position from the relative position in the gate format. You only need to store X and Y relative to the top-left position as the top-left position should already be saved.
Another table is only necessary if the property is not saved for all portals. If you save the sign location for every portal, the position should be saved in the portal table: signX INTEGER, signY INTEGER.

Assuming there is always a sign, but not always a button, and the button location is necessary, there could be an additional table PortalButtonLocation(buttonX INTEGER, buttonY INTEGER) that we just join in the portal view so it becoms null if it does not exist,

The pro to using another table for this is that it allows for further expansion. You would theoretically be able to have a unlimited amount of controls for a portal. I do agree that we could have topleft in the portals database, as topleft will be a thing for every portal.

Assuming there is always a sign, but not always a button, and the button location is necessary, there could be an additional table PortalButtonLocation(buttonX INTEGER, buttonY INTEGER) that we just join in the portal view so it becoms null if it does not exist,

Note that there's the alwaysOn flag and the noSign flag. These would not need to store either a sign position or a button position.

I don't know how you think the database I made would be implemented. But I think it's easiest if we have this separate from the view, So do something like this:

SELECT * FROM portalPosition;

ResultSet set = statement.executeQuerry();
while(set.next()){
    String portalName = set.getString("portalName");
    String netName = set.getString("networkName);
    Network net = factory.getNetwork(netName);
    Portal portal = net.getPortal(portalName);
    if(!portal instanceof RealPortal)
        continue;
    RealPortal realPortal = (RealPortal)portal;
    
    String controlName = set.getString("positionType");
    int x = set.getInt("x");
    int y = set.getInt("y");
    int z = set.getInt("z");
    BlockVector relativeLoc = new BlockVector(x,y,z);
   realPortal.registerControl(relativeLoc , controlName);
}

The pro to using another table for this is that it allows for further expansion.

You should still only store x, y for control blocks and get the real positions from the gate format instead.
If you assume there is any number of types of control blocks, the different types should be stored in their own table to prevent redundancy:

CREATE TABLE PortalPositionType (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    positionName NVARCHAR(8)
);

CREATE TABLE PortalPosition (
    portalName NVARCHAR(180), 
    networkName NVARCHAR(180),
    xCoordinate INTEGER,
    yCoordinate INTEGER,
    positionType INTEGER,
    PRIMARY KEY (portalName, networkName, positionType),
    FOREIGN KEY (portalName, networkName) REFERENCES Portal(portalName, networkName)
    FOREIGN KEY (positionType) REFERENCES PortalPositionType (id)
);

My main motivation for having 3 coordinates for this is for later expansion. I agree with that it is not needed in this current version, but it's just one more value.

I like that system better than the one I wrote otherwise

My main motivation for having 3 coordinates for this is for later expansion. I agree with that it is not needed in this current version, but it's just one more value.

I assume you may need the third coordinate if you want to allow a 3D cube-gate with one sign and button on each of the four (or even six) sides, which is still just one portal keeping one state, or some crazy design like that.
As long as the coordinates themselves, except for top-left are relative to the top-left location, and you are certain we will allow/support designs that makes it necessary, you can add the third coordinate.

Things seem to mostly work as intended now, but there are some problems:

• Looking at the database, the locations of control blocks seem flipped somehow. The sign and button basically switch locations.
• If an empty sign is placed on a stargate, on the button control block, and then the stargate is registered, the sign is protected, but blank (only for always-on stargates). This is probably because the button location is saved for always-on portals, and the exclusion is no longer possible as Gate no longer knows if its portal is always-on.