mysql - Changing database table based on one column

Question

I am new to database design, but not to computers and terminology. I need some help in my database design. I am collecting data from a Global Navigation Satellite System (GNSS) receiver and each packet is differing in sizes depending on which constellation it sees (GPS, GALILEO, GLONASS, etc). There are some common fields across them all, but the way I currently have it set up is all the possible fields are columns, and any field that is unrelated to the new packet is just NULL. This is very inefficient, I just don't know how to go about designing a better way. Thoughts? The main point is as it is now, every time I do a query, I either specify all the fields that are relevant to that specific packet type, or I get a bunch of useless data.

I was thinking of one option, where I have all the common fields in one table, and another table for the unique fields for each packet type. Then have a column that tells what type of packet it is, so when I do a SELECT query I can do a JOIN and only get the data that is relevant to said packet.

标签： mysqlsqldatabasedynamicmariadb

Answer 1

上百列上千行，很多都是NULL？听起来不错。

如果您提供CREATE TABLE;可能会有所帮助 可能有关于数据类型等的提示。

“清理”来自外部来源的数据通常是一个好主意。尤其是如果两个星座以不同的格式提供相同的值。在此示例中，拥有一列并将一列（或两者）从给定格式转换为表的数据类型。有时这是“自动的”。例如，对于FLOATorDECIMAL(6,3)列，“123.456”和“1.23456e2”看起来不同，但进入FLOAT相同。（好的，可能会有四舍五入的差异。）您可以选择使用DOUBLE.

NULLs花费不多。也许主要关心的是您的编程工作。

您的标题“基于列更改数据库” - 很混乱；不要这样做。