Tag Info

New answers tagged

0

It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least. For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL). For historical data, the History Table pattern has proven to be effective. For table ...


0

It is 'rarely' good form to have two tables with the same set of columns. It is 'usually' better to have one table with an extra column that specifies 'dataset' the row belongs to (Bravo/Delta/...). Often it is helpful to think of the schema (table definitions, etc) from an "Entity/Relation" point of view. Start with the Entities such as (I guess) ...


5

I don't think you have a problem with the relationships. I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa. A good way to understand this is to visualize the ...


1

One solution is to assign workers to classification, and classification to company. Simple grandparent to parent to child relationship. A simple join tells you which company employs any particular worker without any ambiguity. This works perfectly well if your goal is to know each worker's single current classification and employer. If you have other ...


0

One option is to use Version Normal Form (vnf). The advantages include: The current data and all past data reside in the same table. The same query is used to retrieve current data or data that was current as of any particular date. Foreign key references to versioned data work the same as for unversioned data. An additional benefit in your case, as ...


0

I would have 2 columns: one for the description and one for the keyword. I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.


0

There's no need to have one table Users and another table Persons. I've never seen a social network that allows a single user to create more than one profile. True, there are people that keep multiple profiles, but these are done by opening multiple accounts (sometimes against the TOS). You could use this model: Table users: user_id (PK) username (IDX, ...


3

There are a couple of concepts which need to be distinguished. One is about structure and the other about schema. Structured data is one where the application knows in advance the meaning of each byte it receives. A good example is measurements from a sensor. In contrast a Twitter stream is unstructured. Schema is about how much of the structure is ...


0

I'll make an assumption first. A real world person could have more than one user in your system? If a real world person (persons table) can have (and should have) only one user, why do you allow a one to many relationship between those two tables? Or I've misunderstood your model or I would create in the Logical Data Model only one table for users AND ...


5

Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW. But additionally I read ...


0

You will need several tables to accomplish your design. One for ads, one for categories and one for the ad properties (make, manufacturer, book title, isbn, etc...). Here is a sample design: Table Advertisement. This will contain the ad instances. ID - primary key Title Category_ID - foreign key to Category.ID. Tells us the ad category. Description ...


0

Store the information in tabular format. It will also help you in validating at application level. Store your field type in property table. and values in columns table. Hope it helps.


0

I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows. The usual technique for this sort of problem ...


0

I would break these queries and have result stored in temporary tables. I would use a stored proc. Have sub-queries like this among many tables will create constrain and potentially some lockings. Also using > on a column in query will for Mysql to do a scan on that column (even if it is indexed). Avoid >,>= or < <= (use between).


7

As stated in comments, I would store the current or effective version of a BlogStory in its respective table and keep all of its previous versions (or past states) in a separate BlogStoryVersion table. In this way, you may find this post helpful since it presents a comparable method for a similar scenario. Business rules In accordance with my ...


1

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user. The model would look like this: This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the ...


0

CREATE TABLE Position ( Id int NOT NULL PRIMARY KEY IDENTITY(1,1), Name nvarchar(255) UNIQUE, IsDeleted bit NOT NULL DEFAULT 0 ) INSERT INTO Position (Name) VALUES ('Titi'), ('Toto'), ('Tata') UPDATE Position set IsDeleted = 1 where Name='Toto' DECLARE @Name nvarchar(255) SET @Name = 'Toto' -- INSERT Position (Name) VALUES (@Name) --RAISES AN ERROR ...


0

Why not somthing simple like this: User enters position 'President' and it does not exist in the table. Insert Into table User deletes position 'President'. Update table set isDeleted = 1 User changes mind and again enters a position 'President'. Update table set isDeleted = 0


3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack. Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, ...


2

Probably easiest would be an INSTEAD OF INSERT trigger. CREATE TRIGGER dbo.FixTable1 ON dbo.Table1 INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @offset INT; -- decimal? Something else? SELECT TOP (1) @offset = offset_m FROM dbo.Table2 -- WHERE ...? ORDER BY [timestamp] DESC; -- terrible column name btw INSERT ...


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


0

Your relation (story_id, version_id, editor_id, author_id, timestamp, title, content , coverimg) is not in 3rd normal form. For every version of your story the author_id is the same. So you need two relations to overcome this (story_id, author_id) (story_id, version_id, editor_id, timestamp, title, content , coverimg) The key of the first relation ...


1

I would favor your first arrangement where you have an Item_Id FK in the Coupon table. You will be able to enforce the "One coupon per item rule" through a unique constraint on the Item_Id column in your Coupon table. You will be able to easily allow multiple coupons per item if business decides to change their mind. Like you said you can easily select ...


5

If you can add a column to the table, the following scheme would almost1 work: CREATE TABLE emails ( UserID integer NOT NULL, EmailAddress varchar(254) NOT NULL, IsActive boolean NOT NULL, -- New column ActiveAddress varchar(254) NOT NULL, -- Obvious PK CONSTRAINT PK_emails_UserID_EmailAddress PRIMARY KEY (UserID, ...


4

The only way to do either of these without schema changes is with a PL/PgSQL trigger. For the "exactly one" case, you can make the references mutual, with one being DEFERRABLE INITIALLY DEFERRED. So A.b_id (FK) references B.b_id (PK) and B.a_id (FK) references A.a_id (PK). Many ORMs etc cannot cope with deferrable constraints though. So in this case you'd ...


4

You are thinking along the right track in wanting to store information about each person. Since you want to record a history of their statistics, the best way to do that is to use another table which will keep one row for each time your record statistics with the date when they were recorded. Now the question becomes how do you want to record each ...


1

The way you have the database setup seems fine. I am not sure this is actually a DB admin question as much as it is a front end question. MySQL or MariaDB would be a fantastic DB to use this on. You can also use MySQL Workbench, which is free, to manage the DB if you want. I think for something this simple, I would just use the command line MySQL ...


3

OK - here's my first pass at answering your question. If a user has role1 in a given assigment and can have another role in a different assigment, then my schema will have to change. If necessary, this can be looked at. I think that some of your requirements will require more than SQL to fulfill - you will probably have to start looking at triggers. You ...


4

I would begin with a little tweak: In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :) First change: CREATE TABLE IF NOT EXISTS `vocabularylists` ( `name` varchar(20) UNIQUE NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, ...


5

I'd recommend storing everything in Planck Lengths. That way you get the most accuracy possible. The Planck length, is the unit of length approximately 1.6 * 10^-35 metres. It is in the system of units known as Planck units. The Planck length is deemed "natural" because it can be defined from three fundamental physical constants: the speed of light, ...


2

I would go with m unit and int datatype, as meters are SI unit and int should be the fastest and the most memory effective.


3

Answers relevant to SQL Server. If talking about query priority: For SQL Server there is by default a FIFO principle adhered to. The first query to run gets priority. However with the resource governor it is possible to not only limit the resources assigned to your sql server instances, it is also possible to create workload groups which can prioritize ...


0

The tutorial you followed was mine and was the accepted answer. However, the other answer given by AaronBrown had a very strong caveat. Instead of having a single point of failure, now you have three. What if one of the servers goes down? Now replication is broken everywhere: How do you replace it easily and align the binary log positions? ...


7

TL;DR: Normalize, normalize, normalize. Joins good, unions bad. Narrow tables with lots of rows good, wide tables with few rows bad. Fewer more general tables good, lots of very specific tables bad. Denormalized structure You're trying to model relations as objects. This can work, but it gets clumsy fast. It looks like a pretty direct mapping of your C++ ...


0

This question and answer on Stack Overflow I think is just what you are looking for. I hope it solves your issue.


0

As a general rule I would recommend updating/creating relationships instead of copying data around to different tables. This is more in line with the relational model, and prevents tables-as-business logic which is what you seem to be doing. Instead of copying data from one table to another I would think about updating a status flag. This could be a ...


1

There is no way to allow for two technicians with the same name and still prevent duplicate technician's from being entered without always providing an additional characteristic that will differentiate them to the DBMS as part of a unique constraint. The DBMS does not reflect reality but instead only what you have asserted to it about reality. It cannot ...


0

For the sake of it I'll describe an approach not mentioned above. It is typically used for temporal data. Not sure it will fit your needs, but here it goes. The idea is to have a copy of your original with two additional attributes, begin_time and end_time: create table ... ( ... , begin_time timestamp default now() not null -- MySQL timestamp deviates from ...


0

I would say that your second suggestion, haivng a TechSpecID in the Vehicles table, makes the most sense. A way of thinking could be, is a Vehicle implementing a TechSpec or is a TechSpec implementing a Vehicle? I.e. what comes first (the Technical Specification) and what is 'using' your Technical Specifications (the vehicles). But, in the end, it's all down ...


0

Deltas The data you are "snapshotting" -- how often does it change? I suggest looking into storing only the "deltas". When some piece of the snapshot does not change at all, the delta is empty, and you can store nothing. For reconstructing a snapshot at some point in the past, the processing is costly -- you need to walk through the versions, applying ...


3

This is a great question! You have a great start on the ERD because you took notes on how the world of the Tutoring Center works. You are right in that all of these entities are related. The difficult part is in developing a sound model of what those relationships are. Before you get into relationships though, you first want to uncover if any of the ...


0

Create a new table, that has all the columns of the existing table, but also a time column, which is set to the value of the time the snapshot was initiated. You're adding a new fact to store about an existing set of facts (each table should store one kind of fact, ideally). The value of the time column identifies each snapshot. If you need to query across ...


0

For MySQL that doesn't have filtered indexes, you could do something like the following. It exploits the fact that MySQL allows multiple NULLs in unique indexes, and uses a dedicated table and a foreign key to simulate a datatype that can only have NULL and 1 as values. With this solution, instead of true / false, you would just use 1 / NULL. CREATE TABLE ...


0

For what it's worth, the USPS has databases available which can be used to disabiguate and check addresses in this country -- which zipcodes cover which towns in which state, which streets (and which street addresses) exist within each zipcode, all the way down to which side of the street the address is on and within which block, and these days probably ...


1

Try to put 'on paper' what you 'fields' that you need. Once you have that then you must group them based on what fields have a same identifier. In you case you might have 3 entities: User with an ID and a name Group with an ID and a name (with values like like pen and paper) Material with an ID, the GroupID and a name For the ToDo you can try yourself to ...


5

As stated in comments, I agree with @Erik and you should normalize the structure of your database defining a specific table to store Order data and another table to store Address related information. The resources previously used to illustrate the approaches suggested here were scattered in different external links, so I have integrated them into a single ...


0

Imagine that, to withdraw some cash from your account, your bank would require you to run SQL queries against a Postgres database at the ATM. "Computer noobs", as you refer to the users of your application, should not be "logging their data" into CSV files, Google Fusion Tables, SQLLite, Postgres, MonetDB, or any other database directly. Instead, you (or ...


4

This answer was compiled from comments to the question. One solution would be to use a FK to the address table in the order table. That will let you see the addresses that were used for the order, and decouples the address from the User's current address. In order to make this work you would have to insert a new address and link that new address to the ...


2

Generally speaking I would recommend a full fledged relational database. This will give you everything you need, and allow you to grow gracefully. As far as what exact engine to use that depends on personal preference/budgetary constraints/hosting requirements/compatability with the application layer/etc. Personally I would probably use the Microsoft ...


1

Yes the code in Update 3 will work and you will be able to select the information you need. If you are comfortable with that then go for it. Keep in mind that means you will have to either duplicate your Painter type in both the sub and super type, or leave Painter as a sub type without a super type. Either way this is a little bit awkward but workable. ...



Top 50 recent answers are included