StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The relational model, as such, does not support "inheritance", which might help solve this problem (though a few DB engines, such as PostgreSQL, do support inheritance).</p> <p>So, I would first ask myself -- do the different types of users need to be able to appear in the same context, at least in some cases? If so, then you can't just copy and paste the "columns in common" to multiple tables (at least not without compromising the integrity checks that you could get in those cases via foreign keys onto a single table).</p> <p>Second question -- is it <strong>ever</strong> possible for a user to hold more than one role? In many cases it would be <strong>unusual</strong> but not utterly impossible, e.g. an employee might also be a supplier or a customer.</p> <p>If I could get no sharp answers to such questions directing me otherwise, I'd set up a users table with only the common fields; and separate tables for suppliers, employees, beta-testers, customers, and whatever other kinds and roles I might have for users, each with just its own specialized columns plus a foreign key onto the users table to pick up the rest.</p> <p>I realize that normalized schemas are out of fashion now, but they've served me faithfully for decades and I have a deep fondness for them -- I only denormalize when I need specific optimizations, and it happens more rarely than one might think!-).</p> <p>One somewhat denormalization that may likely be useful here is an enumeration column in the users table indicating the "main" or "sole" role of each particular use (it could be nullable and maybe uniformly null at the start, if I was pushy enough to have it in from the beginning...;-)... but I'd likely wait to add it if and when performance of some specific queries needed it as a specific optimization, rather than design the schema that way from the start (note that this is a key reason to never use <code>SELECT * FROM</code> in your queries -- if you <code>ALTER TABLE</code> later to add a column, that <code>SELECT *</code> is the one bit that would break!-).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload