CODEWITHSUNDEEP
mysqldatabase-designsplit
user5404027
user5404027

Reputation:

mySQL: tables with many columns

Sorry if this question may be very basic (or even stupid) but I am not very skilled in mySQL. However, I need to create a database for my master thesis so I want to be sure it is the most "elegant" and correct as possible.

I have a dataset with a number of Merger and Acquisition deals with relative information (dealid + 12 columns). I need to add information about the industry of the deal. However, this information comes from 3 different databases that apply to subsets of the M&A: some deals have one classification, some another and some another.

For all I know I have two options: integrate all in the deal table or splitting it in 4 tables (one for the deals and one for each different classification) with 1-1 relations. In the first case I would have a table with more than 50 columns, but the second approach does not look very nice.

Does it one of these approaches make sense? Or is there another way I don't know?

EDIT: right now I have 4 excel tables that I would like to import into mySQL. The main table is the "deal" table: it has the primary key "dealid" and then other columns like "parentname", "targetname", etc. So far, I then have 3 more tables, one for each classification. The primary key is again "dealid", but then every table has specific columns (about 15) since every classification has different features (such as NACE code, NACE code description, industry sector, SIC code etc.).

Thanks in advance.

Upvotes: 0

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In your case, you should specifically have different tables, which are then combined.

Why? Relational databases are designed to handle data in different tables. In general, a single table contains a single "entity", and entities have relationships. You have clearly defined multiple entities in your question -- "deals" and "industries". From the question, I'm not sure if the best solution is 2, 3, or 4 entity tables, but clearly there are more than one.

Next, when you implement them, you want to have keys that connect them. I strongly recommend having an auto-incremented primary key in each table and using that for the relationships.

So, a conceptual example of how to define the tables:

create table industries (
    IndustryId int not null autoincrement primary key,
    IndustryCode varchar(10),
    . . .
);

create table deals (
    DealId int not null autoincrement primary key,
    IndustryId int,
    . . .,
    constraint fk_deals_industry foreign key (IndustryId) references Industries(IndustryId)
);

Upvotes: -1

user3600150
user3600150

Reputation: 77

Very generally, you should aim to have smaller, less-complex tables that you can then combine in many different ways.

https://en.wikipedia.org/wiki/Database_normalization

If necessary you can then use JOINs, VIEWS and other fun stuff to pull together the info that you need for a specific requirement.

Upvotes: 3

Related Questions