Tag Info

Hot answers tagged

4

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak. The reasons to have auto incrementing surrogate int PK's are many: You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works ...


3

This is not a great design. You have a couple of options to improve it: Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints. Put the parent key in the child table. If every child record refers to one master record, ...


2

This would most definitely not be preferred to an auto-increment integer field. For starters: Index width would be degraded significantly with your proposal. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme. Won't ...


2

If a Parent Task and Subtask are essentially identical, save their order in a hierarchy, then yes, a single table is appropriate. You can use a nullable foreign key to the primary key in the same table like this: create table tasks ( task_id bigserial primary key, subject text, ..., parent_task_id bigint null references tasks(task_id) ); If your ...


1

Using an intermediate table is the proper way to do this. With proper indexes and good maintenance it won't hurt performance much at all. Avoid the multi-column solution (refid1, refid2 etc.) - that's repeating groups and not properly normalised. Plus your queries will degenerate into a dog's breakfast of OR clauses and CASE statements, which will hurt ...


1

This sort of question (have new project, how do I...) appears frequently here. I will urge you to do what I would urge everybody who has a new project to do - stand on the shoulders of giants. Check out any/a few/all Open Source projects that do what you are interested in doing (or similar) and learn from their database schemas and adapt what has already ...


1

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL. If so, yes, what you describe is possible, but please, please don't do this. A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g. CREATE TABLE blah( id serial primary key ); is actually shorthand for: ...


1

Hmm, I agree with you, almost, and not with the folks who suggest using an arbitrary integer to identify your rows. Your primary key should be tid char(2), asof datetime, sequence smallint where you control the sequence. That's a perfectly fine primary key because it identifies the row. The burden is on the surrogate crowd to explain the ...


1

Start with: Create a ChartOfAccounts table with the Account code as Primary Key. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transction reversals (if used at all). This is necessary in order to generate ...


1

Jeff Moden has written two excellent articles on SQL Hierarchies here (Hierarchies on Steroids #1) and here (Hierarchies on Steroids #2) which present efficient SQL algorithms for converting hierarchies stored as an Adjacency List (ie children have a Parent pointer - easier to visualize and more efficient to create) to a temporary table organized as nested ...



Only top voted, non community-wiki answers of a minimum length are eligible