There has been at least one post about starting the DB design but I think it would make sense to back up and start with the naming/design convention first. Things like,
Name of the primary key (Id,pk )
Case of the column name (PascalCase I think)
The data type of the primary key (believe we settled on ints)
Foreign key names (ExampleTableId is a foreign key to the primary key of ExampleTable)
Do we want the tables to have a column with the date the row was created? If so I think the name of the row should stay constant.
Same question as above but for the last updated time.
Index naming conventions
Do we want to triggers and change tables that record INSERT, UPDATE, and DELETE operations into a separate table/DB?
I probably missed some questions, but this is where I would start from.
Do we have somebody who’s acting as lead on the database design? If so, can we have that person propose a set of conventions before we start debating individual points? I don’t want this to bog down in the cases where either option would be fine and somebody just needs to pick one. I think of this like coding standards: they tend to prompt a lot of philosophical discussions, but in the end we just need to have one and follow it.
(If there are conventions from the industry for this so we don’t need to roll our own, even better!)
That’d be @Marc.2377 leading schema design. There are conventions, but they often vary by project language so we may want to have similar standards to C#.
I’d like to take the lead on this, with guidance from @Marc.2377 (e.g., he has already indicated PascalCase which matches C# and is arbitrary otherwise), but right now crazy behind schedule…
There are limits based on SQL (probably some “universal” and possibly max. identifier length implementation dependent, etc.) but PascalCase camelCase snake_case tends to be either language-specific or adopted by consensus within a given team or project. So there is a little of everything.
Sometimes the code systems make assumptions about how the tables/columns are named and if you don’t follow those assumptions it makes a lot more work.
Other fun things that can be caused by the intended platform would be table names being case insensitive on Windows and case sensitive on Linux for MySQL.
Generally speaking, if you are not using an ORM, you are writing in raw SQL, and then the table and column names can be “anything”. That being said, normal convention is to stick to alphanumerics (and underscore if using snake_case) and not put in any funny characters that might function differently depending on language and on SQL variant.
Actually, that is, as far as I know, not an issue of operating system but dependent on the SQL variant. And according to https://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive it is actually configurable in many SQL variants. But end result is that our standard will most likely be PascalCase and to treat as case-sensitive.
My idea was, and is, to develop the C# and SQL coding standards more or less at the same time as I developed the (previously proof-of-concept, now repurposed) skeleton project. It got started with the project yesterday (needed some time to set up Postgres, still some adjustments to make but these can wait). @manassehkatz, can I ping you tomorrow or on Friday during the day to see if you can lend me a hand with the SQL bits?
P.S. I’m not strongly in favor of PascalCase for SQL schema definition. Usually, I like to favor whatever is standard practice for the environment (or in this case, db engine) I’m working with. (Same way I name my functions like_this in C++ or JavaScript, but ThisWay in C#.)
Heh, definitely. I don’t know whether the default is sane or not, but this has already bit me once. And not that long ago. Lol
Please don’t forget schema when designing the db… A very underused feature imho (especially in Sql Server where everything is dbo) and definitely worth putting in some design effort.