New answers tagged

1

Check out Percona's tpcc-mysql. Here you can generate a dataset of any size. It should do what you ask.


1

Question 3: Is this best practice? is there any better solution for this type of problems. "Best Practice" might be dependent on the environment. The recommendation would be to try and figure out why a VIEW is "slow". If you can't make it faster, then your solution(s) seem to be fine (after considering the other Business Requirements). Question 1: Is ...


0

You solved your assignment, so not much more to add. However, because you mentioned that you are "super eager to learn database normalization and optimization so I can up my skill in full-stack development," and because I am mostly stuck indoors these days -- I will demonstrate a bit different method to achieve the goal. This method will serve you well ...


0

In addition to Rob Farley's answer, a common pattern to guarantee that PersonTypeId is correct, is to introduce a superkey in PERSONS: ALTER TABLE Persons ADD CONSTRAINT ... UNIQUE (PersonTypeId, Id); ALTER TABLE Persons ADD CONSTRAINT ... CHECK ( PersonTypeId in ('C','D','S')); and extend each of the sub-types with the type attribute. Now ...


2

You need it there so that you know what PersonType is Person is. Without it, you don’t know. Normalisation means that you don’t have PersonTypeName in the Persons table. But PersonTypeID is fine and what you need.


7

To long for a comment, so I'll just add this as a complement to David's solution. Even though the question was of theoretical nature, it may be of interest to see how that can be implemented in real life. In some situations, the anti-reflexive property, and the symmetric property is too strong, but we still want the "uniqueness" property to hold. One common ...


9

Are there official terms for this type of scenario? Yes. This is a Symmetric Relation. And "relation" here has the same meaning as in "Relational Database". An RDBMS is a database management system designed around storing relations. However RDBMSs don't have a native way to store symmetric relations. You either have to store both tuples, eg (a,b) and (...


0

Welcome. If you notice, after your creation of "Sales Types", there are too many duplicate values within. So,...one essential improvement is Let the Sales table have LocationId ProductId RecipientId Date Amount Location table LocationId Location Product table ProductId Product Recipient table RecipientId Recipient Other than this, instead of ...


Top 50 recent answers are included