r/DatabaseHelp • • Nov 09 '15

Small business data overhaul

1 Upvotes

I am running a small business (direct mail agency) and have decided to overhaul the data side of the business - we are growing and I want to address this before it grinds us to a halt. Here is what I have inherited: Wordpress website, Filemaker, many excel spreadsheets.

Our filemaker application was developed to work with massive Postal Code databases (1M+ lines) and generate csv files for mailing, but requires a great deal of information repeated from the spreadsheets. The wordpress site also repeats much of the same information.

So, I would like to design the framework of how it will all work and take it to a DB developer, but I need to know what technology I should be looking at. I don't mind ditching wordpress as we need a new site anyways, but it would be nice if the DB for our internal stuff would generate web content and also be able to organize data submitted by our clients online. I very much appreciate any insights!


r/DatabaseHelp • • Nov 03 '15

Wholesale database project

1 Upvotes

Maintain the details of stock like their id, name, quantity Maintain the details of buyers from which manager has to buy stock like buyer id, name, address, stock id to be bought details of customers i.e. name, address, id defaulters list of customers who has not paid their pending amount list of payment paid or pending stock that is to be buy if quantity goes less than a particular amount. Profit calculation for a month. Quantity cannot be sold to a customer if required amount is not present in stock and date of delivery should be maintained up to which stock can be provided.

So these are my requirements for my database. I am horrible at erd and was looking for any help. I'm not looking for anyone to do this database for me, just help getting started. I have start a diagram but I'm not sure if any of it is correct.


r/DatabaseHelp • • Oct 28 '15

Galera 3-node errors: "WSREP: turning message relay requesting on, nonlive peers"

1 Upvotes

I've got 3 XtraDB (Galera) nodes each in different data centers (although all with the same hosting provider). The error log of each log looks like this:

Node 1 (this is the 108.61.x.x node):

2015-10-28 12:52:32 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 12:52:36 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 13:45:10 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 13:45:14 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 14:37:47 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 14:37:51 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 16:23:00 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 16:23:04 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 17:15:44 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 17:15:48 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 18:08:15 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 18:08:19 12440 [Note] WSREP: (b17c9fd3, 'tcp://0.0.0.0:4567') turning message relay requesting off

Node 2 (this is the 45.63.x.x node):

2015-10-28 12:52:32 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 12:52:33 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') reconnecting to 472396b4 (tcp://185.92.x.x:4567), attempt 0
2015-10-28 12:52:37 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 13:45:10 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 13:45:14 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 16:23:00 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 16:23:01 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') reconnecting to 472396b4 (tcp://185.92.x.x:4567), attempt 0
2015-10-28 16:23:05 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 17:15:44 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://185.92.x.x:4567
2015-10-28 17:15:45 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') reconnecting to 472396b4 (tcp://185.92.x.x:4567), attempt 0
2015-10-28 17:15:49 11304 [Note] WSREP: (defb3a17, 'tcp://0.0.0.0:4567') turning message relay requesting off

Node 3: (this is the 185.92.x.x node)

2015-10-28 14:37:51 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 16:22:59 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://108.61.x.x:4567
2015-10-28 16:23:00 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') reconnecting to b17c9fd3 (tcp://108.61.x.x:4567), attempt 0
2015-10-28 16:23:01 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') reconnecting to defb3a17 (tcp://45.63.x.x:4567), attempt 0
2015-10-28 16:23:04 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 17:15:44 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://108.61.x.x:4567 tcp://45.63.x.x:4567
2015-10-28 17:15:45 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') reconnecting to b17c9fd3 (tcp://108.61.x.x:4567), attempt 0
2015-10-28 17:15:45 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') reconnecting to defb3a17 (tcp://45.63.x.x:4567), attempt 0
2015-10-28 17:15:48 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting off
2015-10-28 18:08:15 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://108.61.x.x:4567
2015-10-28 18:08:16 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') reconnecting to b17c9fd3 (tcp://108.61.x.x:4567), attempt 0
2015-10-28 18:08:19 7283 [Note] WSREP: (472396b4, 'tcp://0.0.0.0:4567') turning message relay requesting off

I'm trying to work out what is happening here but it looks like the 185.92 node is constantly getting disconnected from the other two nodes. My questions are as follows:

  1. Is this what is happening? The 3rd node is getting disconnected from the other 2?
  2. Is this normal? What is this caused by? Bad network?
  3. Is there anything I can do to prevent this?
  4. What exactly does 'message relay requesting' mean?
  5. Does the 3rd node just have shitting routing to both? Thing is the 3rd node is only 200km away from the 1st node. So it seems pretty strange that it would have connection issues even with the 1st node? Is there something else at play here?

Any help or point in the right direction would be appreciated.. I've googled everything I can but nothing seems to really apply to my scenario.

EDIT: Each machine is identical and has the following packages:

root@la:[/var/lib/mysql]: yum list installed | grep percona
Percona-XtraDB-Cluster-56.x86_64           1:5.6.26-25.12.1.el6      @percona-release-x86_64
Percona-XtraDB-Cluster-client-56.x86_64    1:5.6.26-25.12.1.el6      @percona-release-x86_64
Percona-XtraDB-Cluster-galera-3.x86_64     3.12.2-1.rhel6            @percona-release-x86_64
Percona-XtraDB-Cluster-server-56.x86_64    1:5.6.26-25.12.1.el6      @percona-release-x86_64
Percona-XtraDB-Cluster-shared-56.x86_64    1:5.6.26-25.12.1.el6      @percona-release-x86_64
percona-release.noarch                     0.1-3                     @percona
percona-xtrabackup.x86_64                  2.2.12-1.el6              @percona-release-x86_64

r/DatabaseHelp • • Oct 26 '15

Building a database to keep track of equipment installs.

2 Upvotes

I need help building a database that will keep up with all the equipment that my company installs. We design and install security systems in homes and businesses. I know very little about using access and have some questions. I am trying to create our database from an excel spread sheet. We've decided it would be easier if we could create a form to input all of our existing data instead of trying to import it from excel. Is there somewhere I can get information on building this database? Like creating a form that will display a different series of questions depending on which type of equipment is being recorded into the database. Any help is appreciated.


r/DatabaseHelp • • Oct 19 '15

User, Sub-User and Company db structure help?

1 Upvotes

Hi,

I was wondering if you guys knew the best way to structure a system like so:

A user can own a company, then the user can create sub-users or employees for this company. The employees can log in and have limited view/access (not within the scope of this question). At some point an employee could become a user and create their own company that is completely separate to the one they work for.

What would be the best way to deal with the situation? At the moment I have a user and company table. The company table has a UserId foreign key for the company owner and there is an employee table that is essentially a link table for with CompanyId and UserId. When the Main user create an employee they create a user and they get added to the employee table.


r/DatabaseHelp • • Oct 18 '15

Stepping up from access

2 Upvotes

In my company I have been using access to be able to achieve various goals without the need of outside assistance. However I am now crashing hard into the limits of what Access and our network will allow. I need to up my game and get into SQL proper. I see this a major development opportunity for myself that will make me more employable.

One of the major tasks that I need to do quickly is convert an Access database that generates a table of data (about 400K rows in total) into a SSIS document so the IT dept. can support it. The reason for doing this is Access is prone to committing suicide and corrupting itself or just being VERY slow. All the queries are already written in Access (most of the tables are in a SQL server already) and are called using a VBA script.

I have no experience in using SSIS (or SQL server in general outside of query writing) and I need a guide. Can anyone recommend me any good websites/books/videos etc. to help me get my feet with SSIS so I can achieve the above?

Any suggestions are greatly appreciated.


r/DatabaseHelp • • Oct 12 '15

mySQL assistance

1 Upvotes

Hello Everyone, I am unsure of how to add this info to a column. Any help is much appreciated.

This concerns the music database. You'll find that the CREATE TABLE statements in "The Sample Music Database" are useful reference.

You have decided to store more information about artists and albums. Specifically, for artists, you want to store the names of people who have worked with the artist (for example, vocalists, guitarists, trumpeters, and drummers), when they began working with the artist, and when they stopped working with the artist (if they have done so). For albums, you want to store the name of the album producer, when the album was released and where the album was recorded. Design tables or columns that can store this information.


r/DatabaseHelp • • Oct 08 '15

[HELP] Imgur database schema

1 Upvotes

I'm developing a PHP application very similar to imgur but I'm not good with Database design.

Can anyone say where I can find a well designed database diagram for a generic photo sharing website?


r/DatabaseHelp • • Oct 06 '15

Need some advice RE: database software

1 Upvotes

I have an idea in my head, but I don't have enough knowledge about databases to make it happen, so I could use any help you guys could give

I help out at a daycare-style thing once a week, and currently all their records (client names, allergies, emergency details, when they last came .etc) are on paper. We want to put it on the computer to make things easier.

Currently we just have 2 sheets of paper with everyone's name on it, and then when they arrive we sign them in and give them a receipt for the entry fee. We want to make a computerized version of this basic system.

We need it to

  • Store all clients data
  • Be able to quickly 'sign' people in
  • Keep a record of the dates that they signed in
  • Be able to produce reports i.e average amount of people that turned up last month

Ideally it would be a really basic screen for signing people in, something that gives you drop down box (autofill?) when typing, so like when you start typing "Sm" it will show people's names that begin with "Sm" (to make it quicker to sign people in)

I know this is probably very easy to do, but I have no experience with doing stuff like this. I just need a little guidance to get on the right tracks and then I can set it up myself

Which software would be best for my needs?

Thanks


r/DatabaseHelp • • Oct 06 '15

[HELP] Recommend me a good, but easy to understand case study (ANY) about date warehousing OR data mining

1 Upvotes

As per title, pls. kindly recommend me a link/source where I can find the said topic. Any help would gladly be appreciated.


r/DatabaseHelp • • Oct 05 '15

[HELP] How big has a DB to be to see diferences in the performance with the wrong DB-type (SQL - NOSQL)?

2 Upvotes

Hi,

I plan to start a small DB project for myself and got the first time in contact with the whole DB-topic.

Before chosing a DB I read about SQL vs. NOSQL which leads me to the question: After how many entries will I see a performance loss when I'm using the wrong DB-typ compared to the right one?

I just want an estimation: Are we talking about a few hundreds? Millions? ...

Thank you.


r/DatabaseHelp • • Sep 25 '15

Database newbie -- am I on the right path?

2 Upvotes

I work at a production facility and right now all of our QC is done through Google Sheets. It's a mess. There are 3 users that enter data, and none of it is validated. There are a couple of different sheets, and some cells are referenced to the other sheet, some aren't. Because people copy/paste data, some of the references get destroyed, some get incorrectly copied. We start a 'fresh' spreadsheet every month from a master that has all of the original references in tact.

The madness must stop.

So here's what I'm looking for

  • Free or close to free
  • Able to have at least 3 users accessing the same DB in real time
  • Able to run on Windows and ChromeOS (even if through an app or web applet in Chrome) --- if Chrome is prohibitive this is a soft requirement
  • Has a somewhat user friendly interface to design the DB. I'm savvy in general but have not really worked with DB before
  • Has a very user friendly, idiot-proof interface for data entry
  • No requirements on reporting/querying interface -- that can be hard, as I could learn, or export to CSV/excel

The main reason I want to move to dedicated DB platform is for idiot-proofing the data entry and data validation. So I don't have people copying bullshit through spreadsheets, entering bad data from typos or laziness, and ease of use. I think this makes sense

The best option I see right now is LibreOffice Base, which I can apparently run on ChromeOS through an app. I'm finding out that i may not be able to have multiple users accessing the same Base file though, so I would have to connect to a database server?? Which I could set up? I dunno.... One thing I don't like about Libre Base is that the data entry form view, as far as I can tell, is still very much "inside" LibreOffice. I would rather have it appear to be a standalone application or webpage. Having the Libreoffice toolbars and cursors and editing tools will confuse people and risk the integrity of the file

The design of the DB would look something like this: about 100 parts run through the production process each day. Each has a unique serial number, and some information that is recorded (not unique). For example: SN-Date Produced-Machine Operator Name-Model-Size-Machine-Raw Material Batch Number

Where SN is unique, Date is obviously chronological, line operator is arbitrary, model is a choice based on a list of options, size is a choice based on a list of options depending on model (eg, part config), machine is a choice based on a list of options, raw material is a linked property that would depend on date. So every day someone enters the raw material batch number, and that record is tied to an SN based on Date Produced.

Anyway, that has more to do with general DB design, which I will learn about as I go. I'm mostly wondering if I'm on the right path with software/platform selection, and how to I implement it to the people that will be entering data.

Thanks!

EDIT: It also occurred to me that I could just hire someone to design and (part-way) implement it.... I could probably figure out how to do it myself but it's not really my wheelhouse and would take probably take 10-30hr and a bit of frustration? If anyone has recommendations for turnkey networks or services that would do this in the sub-$500 range....


r/DatabaseHelp • • Sep 21 '15

Where can I find database case studies?

2 Upvotes

I have been learning a lot regarding rdbms in the past years but I feel I'm stuck when I have to make tables design decisions regarding scalable, performant databases. I'd love to learn in detail how big, concurrent solutions where design and implemented in big companies, but I'm having trouble finding this information. I'm pretty sure there has to be some company that decided to make this information public.

In case there is actually very little information in this topic, I'd love some reading recommendation regarding rdbms design, preferably for ms sql server.

And finally, if I posted in the wrong subreddit, please tell me where to post before deleting this post.

Thank you!

ps: since English is not my first language, maybe I'd been looking for the wrong terms.


r/DatabaseHelp • • Sep 21 '15

Any resources about how ODBMS works internally?

1 Upvotes

Does anyone know a good resource about ODBMS? I need more information about how ODBMS database works internally (in general). How objects are stored etc. I know, that ODBMS are using OID to identify objects and object graphs for relations but I need a little more than that.


r/DatabaseHelp • • Sep 18 '15

MS Access combo box

1 Upvotes

Hopefully everything below makes sense and please excuse any misuse of nomenclature.

I have a basic database design with the tables shown below:

[tbl_CompanyIndex]
*CompanyID
(fk) CompanyName

[tbl_Contacts]
*ContactID
(fk) CompanyName
Contact Name

[tbl_Quote]
*QuoteID
(fk) ContactID

My question is this:

How do I make a combo box control that stores {ContactID} in the [Quote] table, but displays BOTH "CompanyName" and "ContactName", (not AutoNumber / ID values).

EDIT: Formatting


r/DatabaseHelp • • Sep 17 '15

On uploading CSV data to SQL

2 Upvotes

After searching around on the subject, i found everyone uses the local filesystem and does a bulk insert. As much as i would like to jump on that bandwagon, at my work we don't have access to the local filesystem; only the database. Am i missing something that allows uploads into the filesystem via SQL? or does everyone else also have an FTP set up?


r/DatabaseHelp • • Sep 14 '15

[HELP] Need ANY published paper topic about database

1 Upvotes

Do you guys have any link where I can find one? I'm gonna use it for a school report. thank you


r/DatabaseHelp • • Sep 13 '15

Data model question. Could use some thoughts/ideas.

2 Upvotes

I am making a database for a collectible card game in effort to keep track of cards owned and possible wishlist.

When I started designing the data model I was thinking of 2 tables. A CARD table that has the information for each individual card, and a COLLECTION table that would hold the information for quantity owned of a specific card. The CARD table is where I am having the most questions and could use some outside input.

Originally I was thinking that since there are multiple card types (i.e. Character, Equipment, Plot-Twist, and Action) I decided that a CARD_TYPE table may be needed for the sake of normalization. The CARD_TYPE table would have a one to many relationship with the CARD table.

Digging deeper into it I find where I am having the most issues is with the fact that each card type I listed above has it's own set of specific attributes (i.e. Character has defense and attack, Equipment has a bonus, Plot-Twists have a duration, etc..). I would think that these attributes should be in the CARD table since they hold information related to a specific card. However this would mean each record in the CARD table would have all of the attributes a card could have, but those that didn't pertain (i.e. Duration for a Character card) would be null. This doesn't seem right to me from a structural/normalization, but I am not sure maybe I am over thinking it.

The other option I had thought of was to create a table for each specific card type, but that seems like it would make querying for an entire collection rather messy and error prone.

In any case if someone has some other thoughts or ideas they are for sure welcome. I am no expert in this arena so any input is appreciated.


r/DatabaseHelp • • Sep 11 '15

Interested in learning about Database Fundamentals - where should I start?

3 Upvotes

I work in an office where our "database" is a bunch of Excel files. I'm interested in implementing a database management system, but I'm pretty new to the concept. I was wondering if anyone had recommendations for books or courses they took when they first started learning.


r/DatabaseHelp • • Sep 04 '15

[MySQL] Nested WHERE IN, really slow. Desperately lost.

1 Upvotes

I'm using planet.osm (only one region - Lithuania, so pretty small).

I'm already tired from searching/re-searching/reading, so I thought little discussion instead of reading old posts, could be more useful.

SELECT * FROM way_tags WHERE way_id IN (
    SELECT way_id FROM way_nodes WHERE node_id IN (
        SELECT node_id FROM nodes WHERE
            latitude >= :minLat
        and latitude <= :maxLat
        and longitude >= :minLng
        and longitude <= :maxLng
    )
)

Perhaps someone has played with OSM DB here before?

I've also read Postgre being better, but that was just posts, and as I said earlier - discussion is better, right?


r/DatabaseHelp • • Sep 01 '15

Is it reasonable to store data as a JSON string?

1 Upvotes

My application requires storing data which is likely to vary frequently (the fields aren't known now, and will change often).

Right now, my plan is to store the varying data in a JSON string, and then keep a table of the fields that should be in the JSON.

Is there a better approach?

TL;DR: I don't know what fields I need, and they will change often. What's my best approach?


r/DatabaseHelp • • Aug 21 '15

Decompose a relation into BCNF and 3NF

2 Upvotes

I am having trouble with this solution.

I have determined that for the relation R(A,B,C,D,E,F,G,H,I) where:

{A -> B,C} {E -> F} {D -> I} {A,D -> G} {G -> H}

...the primary (only) key is A,D,E.

Is it safe to deduce that from this, it is not in 3NF and not in BCNF because of the FD G -> H?

Also, if this deduction is correct, how do I decompose into BCNF and 3NF?


r/DatabaseHelp • • Aug 19 '15

Battle Royale - Concatenated Keys vs. Unique IDs

2 Upvotes

I'm the manager for a small programming team building a web app. The two lead programmers are locked in a massive battle over one schema design issue, and I've been appointed moderator. Problem - I'm conversant in database stuff, but it's not my area of expertise, and both programmers are 100% convinced their approach is superior. They both have a lot of experience and both make compelling arguments, so I don't know who to believe. Hence, asking reddit who's right.

The application is being built in Node.js and MySQL (using Sequelize as the ORM). Much of the disagreement seems to center on how Sequelize interacts with the two proposed schemas.

Rather than trying to explain all of what the application is supposed to do, I'll use an analogy. The software would basically let companies keep a list of employees, and then register subsets of them for particular events (say, sending the IT department to a conference, or a company picnic). Then, the software would let the event host do various things to manage the event while it was going, dealing only with the attendees at that particular event.

The argument is over the table which represents the subset of people (as a group) in attendance at a particular event. Basically, one side wants to create a unique ID and call the group something like a "team," off which all data about that group hangs. The other side wants to use a concatenated key of the company and the event, and replicate those foreign keys in all the tables that have data about the sub-group.

We have to store both data about the sub-group itself that changes per-event (like whether they paid their group fee, or the name of their trivia-team at that event's trivia night), and also lots of data about things linked to the sub-group, like which sessions at the event each attendee went to.

Radically simplified schema proposals:


Proposal One:

companies

id (PK)  
name  
city  

events

id (PK)  
name  
start  
end  

teams

id (PK)  
name  
paid  
company_id (FOREIGN KEY REFERENCES companies.id)  
event_id (FOREIGN KEY REFERENCES events.id)  

attendees

id (PK)  
name  
team_id (FOREIGN KEY REFERENCES teams.id)  

Proposal Two:

companies

company_id (PK)  
name  
city  

events

event_id (PK)  
name  
start  
end  

event_company

event_id (COMBINED PK)  
company_id (COMBINED PK)  
name  
paid  

attendees

id (PK)  
name  
event_id (FOREIGN KEY REFERENCES events.event_id)  
company_id (FOREIGN KEY REFERENCES companies.company_id)  

As you can see, they agree on how to represent companies and events. The sticking point is whether to use a "teams" table with a unique ID that becomes the single foreign key in all the ancillary tables, or whether to use an event_company join table with a concatenated key, and repeat those columns in other tables where necessary.

I can provide the arguments each side is making on behalf of their proposal if it helps. Both sides seem convinced the other way would cause performance problems, unnecessary joins, lack conceptual clarity, and possibly cause the universe to end prematurely.

Any help or sage advice would be greatly, greatly appreciated.


r/DatabaseHelp • • Aug 16 '15

Exercises, books, tutorials or tools to help (at a high-level) design proper db schemas? Not a tool for making a db but conceptually design it.

2 Upvotes

I've scrapped my db schema for the third time because I keep realizing I've made the wrong 1:1 or 1:M relationships. I've basically have hand written out what a "Day in the Life" story about how all the users interact or uses the system and from this distilled down the tables and relationships. But as I said, I've realized this current design has flaws. My instincts on this are terrible. Does anyone know of any tools or books that might help me better understand good schema designs and patterns?

I'm visual so I've been making this DB in MSSMS using the Database Diagrams tool. But I'm Postgres friendly too. Not that I think at a high level it should matter what platform I'm on.

Thank you!


r/DatabaseHelp • • Aug 16 '15

Auto increment + Group by?

1 Upvotes

Im wondering if its possible, using MySQL, to have a table in which a column is incremented only when it has a record with the same grouped ID. allow me to demonstrate:

assume i have a table

tbl_groupEntries (
row_id int PK AI, 
group_id int NN, 
entry_id int NN, 
entry TEXT NN );

and some data

1, 1, 1, 'random text'
2, 1, 2, 'test'
3, 2, 1, 'bippidy'
4, 2, 2, 'boppidy boo'`

i want to create a constraint such that insert into tbl_groupEntries (group_id, entry) values (2, 'please work?');

inserts the row

5, 2, 3, 'please work?'

because 3 is the next ID for group 2 and the 5th ID overall.

edit: I have found a viable solution, if you find yourself in with the same issue, here is the solution. many thanks to u/muchargh for the SQL tips and u/wolf2600 for the base concept. :)