Edit Question - KnowledgeHub

Title Body

We are trying to come up with a numbering system for the asset system that we are creating, there has been a few heated discussions on this topic in the office so I decided to ask the experts of SO. Considering the database design below what would be the better option. Example 1: Using auto surrogate keys. ================= ================== Road_Number(PK) Segment_Number(PK) ================= ================== 1 1 Example 2: Using program generated PK ================= ================== Road_Number(PK) Segment_Number(PK) ================= ================== "RD00000001WCK" "00000001.1" (the 00000001.1 means it's the first segment of the road. This increases everytime you add a new segment e.g. 00000001.2 ) Example 3: Using a bit of both(adding a new column) ======================= ========================== ID(PK) Road_Number(UK) ID(PK) Segment_Number(UK) ======================= ========================== 1 "RD00000001WCK" 1 "00000001.1" Just a bit of background information, we will be using the Road Number and Segment Number in reports and other documents, so they have to be unique . I have always liked keeping things simple so I prefer example 1, but I have been reading that you should not expose your primary keys in reports/documents. So now I'm thinking more along the lines of example 3. I am also leaning towards example 3 because if we decide to change how our asset numbering is generated it won't have to do cascade updates on a primary key. What do you think we should do? Thanks. EDIT: Thanks everyone

Tags (comma-separated) Cancel