New answers tagged

0

Rather than address the specific data model aspect of the question (there are already five answers that do a good job of that), I will address the stated goal of this question (after all, context is important). The goal of this data model was stated in a comment on the question: This is my personal design for learning purposes. I am trying to learn ...


0

When doing this sort of design, I think first about the "things", and then about the "relationships". The things are: the people the credit cards and the organisation. The relationships are: Who owns the organisation. Who is a member of the organisation. Which credit card is the owner using to pay the bill You need an entity for each "thing". ...


0

As others mentioned you should use reasonable datatypes, e.g. no BIGINT for students/question (there will never be more than 2 billion students), probably no NVARCHAR(MAX) for questions/answers. You should also add NOT NULL constraints when possible. A clean logical datamodel would result in this: Table Student: StudentID int (primary key, system assigned?...


0

Since first starting to type up an anser I have argued myself into, and out of, a set position. I think this question steps over the border from the science of normalisation into the craft of data modelling. The answer will be determined by how you (or, more importantly, your users) understand the semantics of the situation. Is "extra cheese" a new "thing" ...


0

So, this is an MCQ (Multiple Choice Questionnaire) a test where users will only take it once there are time constraints I think that you've over-engineered on your first pass - you woudn't be the first! :-) A preliminary proposal would be be as follows: CREATE TABLE Student (Student_ID int, Name VARCHAR(25)); CREATE TABLE Question (Question_ID int, ...


1

For databases of a significant size, the normalised logical design is typically created first. Following this, sample queries are run and the database is then denormalised for performance if need be (the physical design). If the gains are minimal, you may elect to stick with the logical design for the simple reason that it is easier to understand and is more ...


1

If you migrate from RDBMS to NoSQL then you will also have problems. They are not interchangeable for all types of usage. When you start a project then just before you start the creation of your data objects you must choose the one that suits best to your needs. Changing later will nearly always cause problems as soon as you use more then the 'standard' ...


0

This is just an "at-a-glance" high-level overview. you have "ID" as your primary key for all your tables. Despite this being anathema to purists (pace Joe Celko and Fabian Pascal), personally, I have no problem with the use of such surrogate keys. However, I would advise that you rename these fields "product_id", "supplier_id" &c. for two reasons ...


1

"Designing database" exam task solution most probably should show how a student can express domain business rules by means of DB data integrity tools (PK,FK). Your solution covers most of them. Missing rules are: (1) Only one Answer is solution to a Question. Bit flag doesn't help without triggers or extra app code. (2) A Student can choose only one Answer ...


0

I think (from my experience) that logs should be treated as separate part of the system and stored in files, or at least de-normalized. You'll probably want to log that sale failed, when the database is down... and you'll probably want to have access to your logs even if something wrong happens with database... for async systems... data is not guaranteed to ...


4

I think you've pretty much nailed it on your initial schema. However, since we're dealing with an MCQ scenario, you don't require an "Answers" table - the answer can be included in the question (along with the two incorrect answers - see below). This will greatly simplify matters. Another smaller change that I would recommend is having a Question_ID field ...


1

As I review the structure you have now I would change the identity columns from BigInt to Int. I suggest you review how many Students, Questions, Answers and Choices you expect and select data types with appropriate sized ranges. I would also replace nvarchar(max) with a more reasonable sized field. As there are limitations with functions on nvarchar(max). ...


1

It sounds like you're talking about a self-referential relationship, especially with the inclusion of "manager" titles. This is a common pattern, especially with employee tables. While they may implement it differently the main RDBMS vendors typically offer recursive capabilities. This means that with a special syntax you can easily query this kind of a ...


0

Fuzzy matching made no sense, given the number of entries I would be dealing with. I needed something to quickly tell me whether or not a duplicate already existed. Consequently, I ended up creating hashes of all unique combinations for a single company. For example, if three different sets of information about Company A were obtained: Set one has hashes ...


1

This is a too broad of a question sadly as each DB Engine might do it differently. To put it very simply most of them work by making a copy of the data you want sorted in a different way. This way when you look for a range of data it's already sorted so the DB Engine can very quickly find what you want without scanning everything. Imagine if you had ...


-1

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information ...


0

Option A is really bad, option B is sufficient, option C overkill. Why not A? Well, what if you need 6 keywords? Queries get ultra complicated when you have to compare all fields, ... Why not C? It will only add extra complexity with another join query, but not really save you a lot of space, memory or whatever you try to save. Space becomes cheaper with ...


5

Easy on the bigint and nvarchar(MAX) Table Student: ID int (primary key, identity) Name nvarchar(800) Table Question: ID smallint (primary key, identity) TextOfTheQuestion nvarchar(800) CorrectAns tinyint FK (to AnswerNum.Num) tested and can make a composite FK to Answer QuestionID, AnsNum I was not sure could do this but it would mean you have ...


0

Why have product_inventory at all? inventory prod_code (foreign key of products.code) | warehouse_code (FK of warehouse.code) | >inventory_count


1

aha! I've recently been reading "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" by Bill Karwin. He talks about this specific antipattern in chapter 8. Trying to create X number of columns in the table is the incorrect solution. The solution is to create a dependent table. You create a column in this table for the multivalued attribute. ...


5

A is not good a table for the keywords ID PK Keyword then have a join table jobID PK FK keywordID PK FK with FK to the two tables


1

Application would be much better place to deal with such cases as Aaron Bertrand said but you can do it with SQL as well . select case when TypeId = 1 then 1 when TypeId = 0 then 2 end from Type where mail = 'name@test.com' UNION ALL SELECT 3 FROM DUAL WHERE NOT EXISTS (SELECT NULL FROM Type WHERE mail = 'name@test.com') ...


1

This sort of question is a fairly regular star on this forum. You essentially appear to be asking about implementing an EAV (Entity Attribute Value) system. Bill Karwin (a big hitter on this forum) has a hilarious image which cleverly shows why this model should normally be avoided. Karwin has written a book about SQL anti-patterns and devotes his first ...


0

If the variants are identified by a variant code at the base of their sku, is the variant code always in the same place? If so, you can always group together with the base sku. However, you might want to look at product grouping. As an example, if your catalog of products is exercise products. And you have fitbits. The Fitbit flex can be sold with ...


0

If you want a relational model you must follow normalization rules. Then the best way is the first option: two tables and "task_id" will be a foreign key in the second table. If not you have duplicate data: id, name and description will be repeated (second example).


1

It depends. If your model is fixed as that which you state in the example data, and always will be, then the first way will work and will enforce that model. If you might later want, or already want to support, a deeper model (i.e. splitting sub-tasks into smaller units of work) then you need the more generic tree structure permitted by the second option. ...


3

What is the cardinality of (this) entity relationship between Employee and Department? Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two entities form having other relationships, (ie. "works-in") that are of different cardinality, one-to-many or many-to-many. Suppose after a year another ...


0

There is a technique called "shared primary key. You can read about it, and pick up examples over on Stackoverflow, by checking out the tag of that name.


3

I think it is a sound design, based on what is called an associative entity. To query: Select A.FieldList..., B.FieldList..., C.FieldList... From A Join AB On AB.AID = A.ID Join B On B.ID = AB.BID Join BC On BC.BID = B.ID Join C On BC.CID = C.ID


1

Just answering your specific scenario. I would create a table for employees and the common attributes they have. Name, DOB etc. I would then create a table for thier skills or qualifications. I would create a foreign key linking to the employee table and use a name/value pair. So you have something like: Empid, qualification, value 1, drivers license, ...


2

I've just recently answered a similar question here. What you want is a People table containing data common to all people contained in your DB with other subsidiary tables providing the additional data unique to different roles some of those people play in the operation of your service. The linked question and answer should provide the details you need. If ...


1

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like Insert ignore into emails values (5,a.a@a.com); If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a ...


1

There wouldn't be a foreign key between the two tables because there is no relationship between them. You have Food (Food_Id), Plate (Plate_Id), and Food_Plate(Food_Id, Plate_Id) Food -> Food_Plate Plate -> Food_Plate Food doesn't have a direct relationship to plate at all. To try a different set of tables that might illustrate this a bit better try ...


1

You will indeed want a third table (food_plate) with two foreign keys, food_id to the food table and plate_id to the plate table. The food and plate tables should not reference each other.


0

The Name of a Person and his/her Bith Date are almost enough to make an unique index with both columns. This way you would garantee 100% of unique individuals in your table, but you could have problems with those miracle situations where two people born the same day with same name. In this case I would recommend you add a new columns called Birth place (...


0

Three most frequent cases when a need arises to make a new table : 1) User can have multiple Phone Numbers. 2) User can change his 'Fisrt/Last Nameover time and the system is requierd to keep track of all 'Fisrt/Last Names and period the 'Fisrt/Last Namewas effective. 3) We generalizePhone Number,E-mail addr,Skype idcolumns as aContact information` and ...


0

The code below is an (simplified) example of how I met the requirement of having a separate table for each item type (e.g. guns, butters, etc) and that every item, regardless of type, has a unique inventory number assigned to it. Items must also never share the same inv number even if they're in separate tables. This solution is following an inheritance ...


1

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence." If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints. Having independent keys is good for quickly getting a record specific to a table, but ...


1

A way to resolve this would be to put the joining of things with tags into separate tables. You would have your original tables users, posts, meta-tags and perhaps items. In addition, you would have user-tags, post-tags, and perhaps item-tags. The later three tables would only have the keys from the specific type table (e.g. user_id) and the key from the ...


5

Sysadmins can see all the data in the instance and there is no way to restrict the permissions of members of the sysadmin server role. The only possible way to hide data from sysadmins is by encrypting it, using a key that is not available on the server. This is exactly what the Always Encrypted feature in SQL Server 2016 does. In SQL Server 2008 your ...


-1

neither base table meta - user_id PK FK user - post_id PK FK post tag user table meta_tags_user_string - user_id PK FK meta - post_id PK FK meta - tag_ID PK FK tag_Def - value tag post table meta_tags_post_string - post_id PK FK meta - tag_ID PK FK tag_Def - value You will need separate meta_tags for ...


2

Static tables are the master tables that are populated with some canned data at the time of creation of the database in a typical system setup. Rather they have a pre defined set of data populated in them that hardly changes. There is no specific term like Static table and Dynamic table, but all tables that are used for inserts, updates and deletes with data ...


2

So what I was looking for, is a new type of system that is related to Datawarehousing: Data Lake System. You can learn more on Wikipedia: A data lake is a method of storing data within a system that facilitates the colocation of data in variant schemas and structural forms, usually object blobs or files. Hadoop and the AWS S3 platform can be used ...


4

What you appear to have is two tables that refer to a common field They have an inferred relationship In the case of a common field then create a master table for Field1 that is nothing but the valid values for Field1. Table1 and Table2 will each have references to the table Field1. If you really need a direct relationship from Table1 to Table2 ...


7

What you describe is not a one-to-many relationship but a many-to-many relationship because the column Field1 is not unique in any of the two tables (and please pick better names to describe the columns and tables, "Table1", "Table2", "Field1" say nothing about the table or the column and are very confusing). I see two options depending on what your ...


0

As a catalogue is related to more than one group you could use a table comparable to your membership table. In this table you store for each specific combination of catalogue and group one access role. As a catalogue is part of multiple roles and multiple roles are the details of one account as far as I understand your basic idea of catalogues being owned ...


-1

Based on how dynamic this sounds like I would suggest looking at a NoSQL data structure. I do not use them so much myself, but looking into something like MongoDB might be worth it. Good luck on your application!


0

ELK Stack is a good choice, even though i use elastic search + gray log for logging purposes. Experience with elastic search, several hundreds of GB are not an issue, u can in any case, easily increase capacity adding new nodes to it. Graylag has an API which you could use in any other app



Top 50 recent answers are included