Using bitwise flag for primary key?

Question

I design the database and thought about the need for a one-two relationship. Traditionally, I did regular PK (like a GUID) and set up relationships, but instead, I was wondering if you would, so don't use the bitwise flag as a PC.

The connection will be lost, but the data itself will describe the relationship.

Example. I have a group table and a user table. Users can have one or more groups:

+------------------------+ | Groups | +------------------------+ | PK | Display Name | +---------+--------------+ | 1 | Group A | | 2 | Group B | | 4 | Group C | +---------+--------------+ +------------------------+ | Users | +------------------------+ | Name | Groups | +---------+--------------+ | Fred | 1 | // Fred is only in Group A | Jim | 3 | // Jim is in Groups A & B | Sam | 7 | // Sam is in all Groups +---------+--------------+ 

Thoughts, comments and suggestions on this design, please?

Answer 1

bad idea ... how would an index work at all? he will have to scan and perform calculations for each value ...

Suppose you wanted to know all the users in group X ... you would need to run a function on each individual line to determine if they were in that group, and not just make a very fast index to find your answer.

Edit: just ... write me a query that uses the index in your table to find all users in group B ... no matter what, you will make a calculation that will force it to do a late, filtered scan

Answer 2

I would refuse to use bit flags. Firstly, you have impaired the ability to easily join these tables, so the definition of group membership will: a) take more time; b) be more complex; c) it is likely to include a more full-screen scan, or at least an index scan.

Answer 3

You will end a series of numbers soon. If you have 64 groups, you are already using 64 bits. I shudder to think about what will happen if you have a million groups.

Another problem is that if you delete a group, you have lost a little. You can reuse this bit later, but it may not be the way you want.

Answer 4

Good idea, I can’t understand how this can be done in a relational database. I think that if you are in SQL, you should pretty much use standard keys, otherwise what will be the value of expensive storage?

It may be better suited for file-based storage, where each table is in a different file. Or, add it as an additional column, but do not put a primary key on it.

PS Can it not fall if two users are in the same groups?

Ryan

Answer 5

IMO is not good. This is a typical many-to-many relationship. If PK is 32 bits, than one user can be no more than 32 groups. Why limit the design?

// Sam in all groups.

Suppose you change the design and make PK 4 insted bits of 3. Now is Sam in "all groups" or only in groups 0-7?

What is the gain?

How would you write queries (joins)? I think you will have problems with this design.

Answer 6

I saw GUIDs that were used as primary keys quite recently, and I think this is a terrible idea. A GUID means “globally unique identifier,” that is, an identifier whose likelihood of replication is to misinterpret Brockmeyer in his OLE book “as likely as a bunch of atoms rushing together in empty space all the time to form a small nut.”

There are good reasons to use a GUID if you really need something globally unique, but most database keys should be unique relative to a given database. In this case, the often-generated sequential integer key is often sufficient, and it consumes much less resources. In fact, neither sequence identifiers nor GUIDs have any inherent meaning, so it’s better if you can use elements that really really identify the object in question as a primary key, if you can (although there is a school of thought that says all the elements must have a content-independent key, such as a sequence or even a GUID).

Battles have several obligations as primary keys. As already mentioned, you can decide that you need extra bits. You may encounter collisions. Databases are generally poorly bit-wise and cannot be migrated even then. Finally, indexing algorithms for the database of your choice may not be able to optimize bit keys well.