Hot answers tagged schema
7
You could consider a cart to be a storage location.
Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so:
If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table.
This is not a ...
6
You can get rid of the anomaly by changing the FOREIGN KEY constraint (from Widgets to Carts) to include the StorageLocationID:
CREATE TABLE Widgets
( widgetID NOT NULL
, storageLocationID NOT NULL
, cartID NULL
, PRIMARY KEY (widgetID)
, FOREIGN KEY (storageLocationID)
REFERENCES StorageLocations ...
3
Your diagram has a table for Widgets with a StorageLocationID and a CartID. Not sure that makes sense logically, since the StorageLocationID refers to something physical and fixed, and the CartID can move over time.
You don't seem to be tracking things over time though, so perhaps that is the disconnect.
But when I think of this scenario I think that a ...
3
Without knowing more about the business requirements, with the given structure you defined I would probably go with something like this:
Expense (Id, DetId, TypeId, Amount, Date, EmployeeId)
ExpenseType (Id, Name)
Where Expense.TypeId is a foreign key constraint to ExpenseType.Id. ExpenseType could contain rows for daily or monthly expenses. This is ...
2
I would suggest doing it in the application rather than in the database with a trigger, only because it's not integral to the structure of the database; rather it's for performance reasons and I don't think doing it in the database will actually improve performance of the query that's running (it might slightly improve performance of the actual insert/update ...
1
I recommend reading the following link on Database Inheritance: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx
Databases don't naturally fit hierarchies - they do work, but it's not their strong suit.
In your case I would change shops_and_items for
**products**(product_id (PK), shop_ID (FK), ... ...
1
Looks like you are describing a subtype/supertype structure.
Start by creating a reference table to hold the different types of response types you can create. This table can be expanded easily if you come up with more types later on.
CREATE TABLE survey_response_types (
`response_type_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`type` VARCHAR(255) ...
1
I have answered questions like this before
Dec 21, 2011 : Table compression in InnoDB?
Feb 03, 2012 : How to recover MySQL table structure from FRM files
Apr 23, 2012 : MySQL: how to restore table stored in a .frm and a .ibd file?
I have referred to a blog from Chris Calendar on connecting .ibd files back to the data dictionary.
If you do not have the ...
1
There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level ...
Only top voted, non community-wiki answers of a minimum length are eligible
