New answers tagged

2

I would not go too far in determining how people should spell their own name. There is probable more variation in names in the world than you can capture with your automated rules. I know someone named "d'Agano" and he is not amused if you 'correct' his name by capitalizing the 'D', remove the apostrophe or lowercase the 'A'. There are scores of people ...


1

My two cents: Note: don't confuse concatenating multiple values into a single string and use it as a PK with having multi-column PK. That said: Concatenating multiple non-key data into a string to populate a key column has several disadvantages: People could guess IDs for hacking purposes When the non-key data changes the key is left outdated and has to ...


1

Note: The top part of this answer is an answer to the question before the view-example has been posted by the original poster. For an updated version, taking the view in mind, check the bottom of this answer, under the line. I don't like you passing a query to a database abstraction layer actually covering two databases. An abstraction layer dealing ...


6

Your mistake is you want to use the Name instead of ID because Name is more Readable. What you should doing is making your ID more readable. Think of it as RoleCode instead of RoleID Role(RoleCode Pk, Name, Other_Columns) Command(CommandID PK, RoleCode FK, Other_Columns) Sample Data RoleCode Name Other_Columns -------- ---- ...


8

Using names as FKs is unorthodox at the least. Usually FKs point to the FK of the parent table. You might as well make name the PK of both tables and get rid of the ID altogether You've found one of the disadvantages of using surrogate PKs where good business keys exist. You should be able to read and understand the association table. After all the data ...


0

Here is how I would approach the situation: You keep your Users table What you consider as Roles are, basically, groups of Users. So, we create a new table Groups What you call PermissionLevel is the equivalent to Roles that members of Groups can have (eg. a user who is Tech_Admin can have the role of Tech_Account and Non_Tech_account I would replace the ...


1

In layman's terms: The whole point of relational databases is normalization. Normalization means separating tables into different tables in order to avoid udpate anomalies and to speed up look ups, reduce redundancy etc. So your question really is "should a database be normalized?" and the answer is "of course". Obviously there would be exceptions in a ...


1

You are not thinking too much - your questions are very relevant for the design. But they are not technical questions, they are questions about the requirements. Should your system support payment plans or polygamous marriages? These are not technical questions, these are business decision. As for spouses though it is obvious you have to support unmarried ...


1

Your current design is this: You should ask yourself: does entity PERMISSION_LEVEL represent actual levels? Is they are actual levels it means that when a user is granted two different roles and both roles have the same command but with different levels, the app should take the highest level of them. In role-permission models with no permission level, ...


2

It depends is the only real answer here. The reason you ask this question and you are in doubt is because of the fact that you are unclear about the specs to build. Example questions you should ask on orders example. Initial client / product owner request: "We want to see if the orders are paid in the orders screen." Your questions: Cool, sure we can ...


0

I am not sure what purpose requires the has_xxx fields. It is common to use nullable columns where the column may not have a value. You will need to do this anyway for the columns corresponding the the has_xxx fields. If the value is not NULL then has_xxx had better be true, and if the value is NULL, then has_xxs had better be false. This makes the ...


1

This is an optional 1 to 1 relationship or a 1 to (0,1) relationship. A true identity relationship would be 1 to (1,1). I find this notation useful in understanding the scale of the relationship. A one to many relationship could be 1 to (0,10), 1 to (1,5), 1 to (1, *). The fist digit is always 0 (optional) or 1 (mandatory) while the second specifies an ...


1

Does this look like a reasonable design for Role and Permission management ? The term "reasonable" is somewhat ambiguous here. Fit for purpose This design achieves it's purpose. It enables to determine what operations any logged-in user can do on any specific business object : Users are identified Roles are identified Users are assigned to roles ...


0

You should use joins, and limit the columns you select, AND run multiple selects in the single query : (changed your structure slightly to reduce example code) SELECT t1.* FROM t1 LEFT JOIN t2 on t2.t1_id = t1.id WHERE t1.id > 123 and t1.id < 321; SELECT t2.* FROM t1 INNER JOIN t2 on t2.t1_id = t1.id WHERE t1.id &...


3

Such kind of questions cannot be answered in a sensible manner on an abstract level, without the surrounding context. One has to try this out using the real system: a real database, filled with real data, a real network, a real client machine running a real client application. Then it will be possible to profile both approaches and compare them. And keep in ...


0

I'd leave them in a single table if that's where they are now. Splitting the table might make sense if you have some BLOB fields you want to isolate, but you didn't mention that. I've seen tables like this (with many columns that are mostly NULL) with almost 100 columns. They're ugly to work with, but you should ask yourself how much labor would it ...


0

I think you're going down the right path. I'm not sure I'd use nullable/optional fields though. In my mind, NULL means "unknown", not "ANY". Using your example data you could create a table DocID Org Dept Posn 1 X 1 any 1 X 1 A // redundant, due to the record above 1 X any A 1 ...


0

While you can save files in the DB, or save links to the files.. this is not what you want here. SQLServer even has the feature of linking the files on disk into its tables so the fies on disk appear to be part of the DB directly.. but that's pretty advanced usage. What I think you want to do is to store the data that you currently have in text files into ...


3

Given the fact that your files are just text files containing text lines, my suggestion is this: Don't save links to the files Don't save the files themselves Save the list items to the database Here's a simple physical model to store lists: In the case we were talking about large files like video files or large images, it's better to just store the ...


1

I see two possibilities for creating a new key: Generate A UUID5, which is based on the SHA-1 hash of a namespace identifier (which is a UUID) and a name (which is a string). Use your original UUID and a unique string combination within your record, or Generate a SHA512 hash of your entire record, encode it to a base64 representation, and append the ...


2

It is bad practice to denormalize as you state. Depending on your search criteria, any one of the four tables could be chosen by the query optimization routine. If you are selecting on a package selected from a drop-down list, there should be no reason to add the packages table to the query as you can match the package_id in the package_durations table. ...


2

I want to be able to add, remove, and update: the users (especially their passwords/key-encrypting-keys), the groups, the data-encrypting keys, and the data. I think you can gain the required independence between entities by using a mixture of asymmetric and symmetric encryption. The data storage requirements are quite high but only little data need to be ...


1

The issue I have with the answer to the question you posted is that if you view the requirements of the OP for security, nowhere does it actually mention that each user must provide their own decryption keys. In fact the question even goes so far as to suggest that they explicitly do not want the users password to be a determination in the encryption of ...


1

Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody. Table Structure Table_Offer -------- ID FK Name start time end time MandatoryGroup -------- ProductId (FK to product) MixGroup1 -------- ProductId (FK to product) MixGroup2 -------- ProductId (FK to product) Table_offerDetails --------------...



Top 50 recent answers are included