It might not be a terrible idea to auto increment a primary key on those reference tables for whenever they get big. You'd just put a FK constraint on those one-to-many relationships in the other tables. create table if not exists "publication_shared_by" (
I'm not sure if the identity(1,1) syntax is correct (I work primarily with a T-SQL system).
); id integer identity(1,1), --primary key will help when this table becomes large
publication_id integer, -- fk into publication, NOT a pk, one to many
username text --create a non-clustered index to the 'username' table or something
SERIAL is the PostgreSQL version. Right now, the Arc code creates its own ids. It will have to be changed to assume auto-increment. And yes, we may, eventually. We also need primary and foreign key constraints, not null constraints, indices, and other things. I plan to add all those after the initial conversion and the live app is pulling from SQL. Having zero constraints initially makes the conversion faster, which is important because I plan to bring hubski down to convert. Right now, it takes about an hour. I could do it live, but that would take extra code, and an hour downtime at 04:00 CDT is acceptable. If you're referring to 'search_text' etc when you say 'reference tables,' those will be going away when the real search solution is done. None of the other one-to-many tables will be very big. I'm also on the fence about auto-increment. I'm not primarily a database dev, but as a developer, I generally disapprove of logic in databases. But that's a different argument.
Haha, I'd call you a blasphemer! Let the application generate the Id?! What kind of voodoo are you throwing out here?! (jk) I'm slowly finding my love for software development residing within the confines of database work. Most of our legacy application code is generally stable and a lot of tasks I find myself doing is data migration and query optimization. Before that there was a ton of handwritten replication (don't ever do it, please; but if you have to give me a call) but thats all out the window with an in-house automated solution. Almost all my coworkers are database enthusiasts, a few of which I would call fanatics. A couple posses a lifetime of wisdom within the subject that has left me really inspired. I'm super stoked about you throwing up the schema, thanks a lot!